DB Tutorial

Rabu, 21 April 2010

Menggabungkan isi field menjadi satu String di MS SQL 2005

Ketika kita membutuhkan data dari isi kolom menjadi satu string. Kita bisa menggunakan sintak for xml.

Contohnya saya akan menggabungkan isi dari field name pada sys.databases :

SELECT name
FROM sys.databases
WHERE database_id > 4

Name

Temp

Liquibase

NorthWind

EMPLOYEES

Dengan query berikut kita bisa menggabungkan nama tersebut :

SELECT Stuff((SELECT ', ' + name
              FROM   sys.databases
              WHERE  database_id > 4
              ORDER  BY name
              FOR XML PATH('')), 1, 2, '') AS namelist;

Namelist

EMPLOYEES, Liquibase, NorthWind, Temp

Bagaimana jika kita ingin menambahkan tanda kurung siku pada setiap nama seperti berikut: <EMPLOYEES>, <Liquibase>, <NorthWind>, <Temp>

Jika kita menggunakan syntax seperti diatas :

SELECT Stuff((SELECT ', <' + name + '>'
              FROM   sys.databases
              WHERE  database_id > 4
              ORDER  BY name
              FOR XML PATH('')), 1, 2, '') AS namelist;

Hasilnya tanda < dan > muncul menjadi &lt dan &gt :

namelist

Untuk mengatasi itu, kita bisa menambahkan sedikit syntak forxml nya. Berikut querynya :

SELECT
STUFF(
(SELECT ', <' + name + '>'
FROM sys.databases
WHERE database_id > 4
ORDER BY name
FOR XML PATH(''), root('MyString'), TYPE
).value('/MyString[1]','varchar(max)')
, 1, 2, '') AS namelist;

atau

SELECT
STUFF(
(SELECT ', <' + name + '>'
FROM sys.databases
WHERE database_id > 4
ORDER BY name
FOR XML PATH(''), TYPE
).value('(./text())[1]','varchar(max)')
, 1, 2, '') AS namelist

Hasilnya :

namelist

Selasa, 20 April 2010

Query Ranking dalam MS SQL Server 2005

Ranking atau peringkat, manusia senang membuat ranking. Dalam MS SQL Server 2005 pun tersedia beberapa fungsi untuk menampilkan hasil query dengan nomor ranking. Untuk contoh-contoh query ranking saya menggunakan Entity Relationship berikut :

Saya membuat view berikut untuk menjumlahkan SubTotal dari setiap order ID :

CREATE VIEW [dbo].[viewSumSubTotalPerOrder]
AS
  SELECT orderid,
         SUM(subtotal) AS total
  FROM   dbo.orderitems
  GROUP  BY orderid

Untuk menampilkan query ranking, kita bisa menggunakan query ini :

SELECT c.name,
       o.dateordered,
       o.orderid,
       vw.total,
       Row_number() OVER (ORDER BY total DESC) AS bestcustomer
FROM   viewsumsubtotalperorder AS vw
       INNER JOIN orders AS o
         ON o.orderid = vw.orderid
       INNER JOIN customers AS c
         ON c.customerid = o.customerid

Akan menghasilkan :

Name	DateOrdered	orderId	Total	BestCustomer
Kurniawan	00:00.0	11	450000	1
Slamet	00:00.0	6	240000	2
Kurniawan	00:00.0	10	220000	3
Rangky	00:00.0	3	100000	4
Firman	00:00.0	8	60000	5
Rangky	00:00.0	1	45000	6
Rico	00:00.0	2	45000	7
Slamet	00:00.0	5	40000	8
Fajar	00:00.0	7	30000	9
Firman	00:00.0	9	15000	10
Rico	00:00.0	4	2000	11

Yang ini query ranking yang dibagi-bagi berdasarkan nama customernya :

SELECT c.name,
       o.dateordered,
       o.orderid,
       vw.total,
       Row_number() OVER (PARTITION BY c.customerid ORDER BY total DESC) AS bestcustomer
FROM   viewsumsubtotalperorder AS vw
       INNER JOIN orders AS o
         ON o.orderid = vw.orderid
       INNER JOIN customers AS c
         ON c.customerid = o.customerid

Menghasilkan :

Name	DateOrdered	orderId	Total	BestCustomer
Rico	00:00.0	2	45000	1
Rico	00:00.0	4	2000	2
Rangky	00:00.0	3	100000	1
Rangky	00:00.0	1	45000	2
Fajar	00:00.0	7	30000	1
Slamet	00:00.0	6	240000	1
Slamet	00:00.0	5	40000	2
Firman	00:00.0	8	60000	1
Firman	00:00.0	9	15000	2
Kurniawan	00:00.0	11	450000	1
Kurniawan	00:00.0	10	220000	2

Tapi kurangnya adalah, hasil ranking ini tidak bisa di masukan didalam kondisi "where" ataupun didalam expresi "order by". Untuk melakukan itu kita harus memasukan dulu ketable terpisah atau masukannya keview.
Berikut contoh querynya :

CREATE VIEW [dbo].[viewBestCustomers]
AS
  SELECT c.name,
         o.dateordered,
         o.orderid,
         vw.total,
         Row_number() OVER (ORDER BY total DESC) AS bestcustomer
  FROM   viewsumsubtotalperorder AS vw
         INNER JOIN orders AS o
           ON o.orderid = vw.orderid
         INNER JOIN customers AS c
           ON c.customerid = o.customerid

SELECT name,
       dateordered,
       orderid,
       total,
       bestcustomer
FROM   viewbestcustomers
WHERE  bestcustomer BETWEEN 3 AND 5

Name	DateOrdered	orderId	Total	BestCustomer
Kurniawan	00:00.0	10	220000	3
Rangky	00:00.0	3	100000	4
Firman	00:00.0	8	60000	5

Semua hasil diatas dihasilkan dengan syntax ROW_NUMBER(), selanjutnya saya akan menampilkan perbedaan dengan menggunakan syntax RANK() dan DENSE_RANK :

Menggunakan RANK() :

SELECT c.name,
       o.dateordered,
       vw.total,
       Rank() OVER (ORDER BY total DESC) AS bestcustomer
FROM   viewsumsubtotalperorder AS vw
       INNER JOIN orders AS o
         ON o.orderid = vw.orderid
       INNER JOIN customers AS c
         ON c.customerid = o.customerid

Menghasilkan :

Name	DateOrdered	Total	BestCustomer
Kurniawan	00:00.0	450000	1
Slamet	00:00.0	240000	2
Kurniawan	00:00.0	220000	3
Rangky	00:00.0	100000	4
Firman	00:00.0	60000	5
Rangky	00:00.0	45000	6
Rico	00:00.0	45000	6
Slamet	00:00.0	40000	8
Fajar	00:00.0	30000	9
Firman	00:00.0	15000	10
Rico	00:00.0	2000	11

Menggunakan DENSE_RANK :

SELECT c.name,
       o.dateordered,
       vw.total,
       Dense_rank() OVER (ORDER BY total DESC) AS bestcustomer
FROM   viewsumsubtotalperorder AS vw
       INNER JOIN orders AS o
         ON o.orderid = vw.orderid
       INNER JOIN customers AS c
         ON c.customerid = o.customerid

Menghasilkan :

Name	DateOrdered	Total	BestCustomer
Kurniawan	00:00.0	450000	1
Slamet	00:00.0	240000	2
Kurniawan	00:00.0	220000	3
Rangky	00:00.0	100000	4
Firman	00:00.0	60000	5
Rangky	00:00.0	45000	6
Rico	00:00.0	45000	6
Slamet	00:00.0	40000	7
Fajar	00:00.0	30000	8
Firman	00:00.0	15000	9
Rico	00:00.0	2000	10

Yang terakhir adalah, query untuk membagi hasil ranking berdasarkan berapa grup yang kita mau :

SELECT productid,
       name,
       price,
       Ntile(4) OVER (ORDER BY price DESC) AS quartile
FROM   products

Menghasilkan :

ProductID	Name	Price	Quartile
3	Table	100000	1
6	Shoes	40000	1
2	Chair	30000	2
5	Bag	20000	2
7	Hat	15000	3
4	Pen	2000	3
1	Book	1500	4
8	Pencil	500	4

Bagi yang mau mencoba-coba, saya sertakan juga data mentahnya disini. File yang saya upload berextensi .doc, ubah saja menjadi .sql.

Semoga tutorial sederhana ini membantu dalam pembuatan report, atau query sederhana yang anda inginkan.

Rabu, 14 April 2010

Mencari nama Stored Procedure atau Mencari Text didalam SP

Saya biasa membuat satu SP buat mencari nama SP atau mencari text didalam SP tersebut. Berikut SP yang saya buat :
1. Find_SP

CREATE PROCEDURE Find_sp @StringToSearch VARCHAR(100)
AS
  SET @StringToSearch = '%' + @StringToSearch + '%'

  SELECT DISTINCT so.name
  FROM   sysobjects so (nolock)
  WHERE  so.TYPE = 'P'
         AND so.name LIKE @StringToSearch
  ORDER  BY so.name

2. Find_Text_In_SP

CREATE PROCEDURE Find_text_in_sp @StringToSearch VARCHAR(100)
AS
  SET @StringToSearch = '%' + @StringToSearch + '%'

   SELECT DISTINCT so.name
  FROM   sysobjects so (nolock)
         INNER JOIN syscomments sc (nolock)
           ON so.id = sc.id
              AND so.TYPE = 'P'
              AND sc.TEXT LIKE @stringtosearch
  ORDER  BY so.name

Selasa, 01 Desember 2009

Membuat Kode Incremental di MSSQL 2005

Ketika kita ingin membuat kode transaksi pada suatu table, mungkin saja kita mau membuat format kode sendiri. Misalnya A0001, A0002, A0003, dst.

Kita dapat membuat kode incremental tersebut dengan cara :

DECLARE @count AS INT
SET @count = 1
WHILE @count <= 10
  BEGIN
      SELECT 'A' + REPLACE(Str(@count, 4), ' ', '0')

       SET @count = @count + 1
  END

Selasa, 16 Juni 2009

Siapa yang menggunakannya?

Terkadang ketika kita ingin mengubah suatu code di Store Procedure(SP) atau Function di SQL Server 2005, kita khawatir apakah SP atau Function tersebut dipakai di SP atau Function lain.

Untuk melihat hal itu kita dapat melakukan query seperti berikut :

SELECT *
FROM   sysobjects a,
       syscomments b
WHERE  a.id = b.id
       AND b.TEXT LIKE '%SP_GeAmount%'

Semoga Membantu ^_^

Select data dari Excel di SQL Server 2005

Ketika kita sudah terbiasa dengan syntax query SQL, pengolahan data rasanya lebih mudah jika dilakukan dengan SQL query. Modifikasi dapat dilakukan dengan SQL query sehingga data yang tampil sesuai dengan yang kita inginkan.

Pernah suatu ketika saya memperoleh data yang banyak dalam dua file Excel. Saya harus membandingkan data-datanya. Ya...., saya mencari cara untuk memindahkan data Excel tersebut kedalam SQL. Berikut caranya :


SELECT *
FROM   OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]



SELECT *
FROM   OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                  'Excel 8.0;Database=C:\test\xltest.xls',
                  [Customers$])



SELECT *
FROM   OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                  'Excel 8.0;Database=C:\test\xltest.xls',
                  'SELECT * FROM   [Customers$]')

Ketiga syntax diatas dapat di run. Customers$ itu adalah nama sheet dalam file Excelnya. Nama sheet tersebut diakhiri dengan tanda $. Data pada row pertama di Excel diambil sebagai Nama Kolom pada Sql Server. Namun biasanya muncul error seperti berikut ketika menjalankan syntax diatas:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Untuk mengatasinya kita dapat mengaktifkan As Hoc Distributed Queries dengan sp_configure :


SELECT *
FROM   sys.configurations
ORDER  BY name;
GO

Sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

Sp_configure 'Ad Hoc Distributed Queries', 1;
GO

RECONFIGURE;
GO

Untuk memasukan data Excel tersebut kedalam table sementara di SQL server kita dapat menggunakan :


SELECT *
INTO   #temp
FROM   OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                  'Excel 8.0;Database=C:\Test\data.xls',
                  'SELECT * FROM   [Sheet1$]')

Semoga cara ini bisa membantu kawan-kawan untuk mengambil data dari excel.

DB Tutorial

Rabu, 21 April 2010

Menggabungkan isi field menjadi satu String di MS SQL 2005

Selasa, 20 April 2010

Query Ranking dalam MS SQL Server 2005

Rabu, 14 April 2010

Mencari nama Stored Procedure atau Mencari Text didalam SP

Selasa, 01 Desember 2009

Membuat Kode Incremental di MSSQL 2005

Selasa, 16 Juni 2009

Siapa yang menggunakannya?

Select data dari Excel di SQL Server 2005

Cari Blog Ini

Label

Pengikut

Arsip Blog

DB Tutorial

Rabu, 21 April 2010

Menggabungkan isi field menjadi satu String di MS SQL 2005

Selasa, 20 April 2010

Query Ranking dalam MS SQL Server 2005

Rabu, 14 April 2010

Mencari nama Stored Procedure atau Mencari Text didalam SP

Selasa, 01 Desember 2009

Membuat Kode Incremental di MSSQL 2005

Selasa, 16 Juni 2009

Siapa yang menggunakannya?

Select data dari Excel di SQL Server 2005

Cari Blog Ini

Label

Langganan

Pengikut

Arsip Blog