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

<EMPLOYEES>, <Liquibase>, <NorthWind>, <Temp>

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

<EMPLOYEES>, <Liquibase>, <NorthWind>, <Temp>

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.

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