Eksekusi Query berulang kali

Sesekali kita ingin mengeksekusi query secara berulang-ulang, entah itu untuk testing atau untuk keperluan mengumpulkan data. Pertama kali saya menggunakan metode looping(while) untuk melakukan query berulang-ulang. Sampai akhirnya saya menemukan suatu cara dengan syntax GO, berikut contohnya :

CREATE TABLE dbo.data 
  ( 
     id    INT IDENTITY (1, 1), 
     ratio DECIMAL(19, 3) 
  ) 

GO 

INSERT INTO dbo.data (ratio) VALUES(Rand()) 

GO 20 


Yah dengan query ini setidaknya saya menghemat waktu saya daripada membuat query looping. Selamat mencoba, thanks.

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.

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.