Pada session ini kita akan membuat contoh sederhana pivot query di SQL, kita menggunakan pivot query ketika kita perlu untuk mentransform data dari level baris ke dalam kolom data.
Pivot adalah sebuah table interaktif yang mengkombinasikan dan membandingkan nominal/angka dari sebuah data. Kita bisa merotate baris dan kolomnya untuk melihat perbedaan summary dari sebuah sumber data.
Buat table database di SQL Server. Pada contoh dibawah saya beri nama tabel INVOICE. Tabel INVOICE memiliki tiga kolom data yaitu DT, PRODUCT dan AMOUNT.
Script CREATE tabel di SQL Server :
CREATE TABLE [dbo].[INVOICE]( [DT] [datetime] NULL, [PRODUCT] [varchar](20) NULL, [AMOUNT] [numeric](18, 0) NULL )
Setelah tabel INVOICE dibuat lakukan query INSERT datanya dengan meng-execute sript dibawah ini.
INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A41300000000 AS DateTime), N'A', CAST(100 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A41300000000 AS DateTime), N'B', CAST(90 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A41400000000 AS DateTime), N'C', CAST(80 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A41400000000 AS DateTime), N'A', CAST(100 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A41400000000 AS DateTime), N'B', CAST(90 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A41500000000 AS DateTime), N'A', CAST(100 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A41500000000 AS DateTime), N'B', CAST(90 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A43200000000 AS DateTime), N'A', CAST(100 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A43300000000 AS DateTime), N'A', CAST(100 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A43300000000 AS DateTime), N'B', CAST(90 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A43300000000 AS DateTime), N'C', CAST(80 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A43400000000 AS DateTime), N'A', CAST(100 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A43400000000 AS DateTime), N'B', CAST(90 AS Numeric(18, 0))) INSERT [dbo].[INVOICE] ([DT], [PRODUCT], [AMOUNT]) VALUES (CAST(0x0000A43500000000 AS DateTime), N'C', CAST(80 AS Numeric(18, 0)))
Kalau kita query SELECT table INVOICE dari hasil INSERT di atas maka akan menghasilkan result seperti gambar berikut ini.
Dari data seperti gambar di atas, kita bisa membuat query yang menghasilkan data produk per tanggal dan produk per bulan dengan nominal amountnya menggunakan pivot queri.
Berikut script pivot query produk per tanggal di bulan January :
SELECT * FROM ( SELECT DT AS [DATE] ,PRODUCT ,AMOUNT FROM INVOICE WHERE MONTH(DT)=1 ) X PIVOT ( SUM(AMOUNT) FOR [DATE] IN ([2015-01-01],[2015-01-02],[2015-01-03]) ) P
Result query setelah di-execute :
Script pivot query produk per bulan :
SELECT * FROM ( SELECT DATENAME(MONTH,DT) AS [MONTH] ,PRODUCT ,AMOUNT FROM INVOICE ) X PIVOT ( SUM(AMOUNT) FOR [MONTH] IN ([JANUARY],[FEBRUARY]) ) P
Result query setelah di-execute :
Dari script pivot query diatas, kita masih menggunakan fix column. Berikut ini pivot query dengan kolom dinamis.
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', ' + QUOTENAME([MONTH])
FROM (SELECT DISTINCT DATENAME(MONTH,DT) AS [MONTH] FROM INVOICE) AS C;
SET @sql = N'
SELECT PRODUCT,' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT DATENAME(MONTH,DT) AS [MONTH]
,PRODUCT
,AMOUNT
FROM INVOICE) AS X
PIVOT
(
SUM(AMOUNT) FOR [MONTH] IN ('
+ STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
+ ')
) AS P;';
PRINT @sql;
EXEC sp_executesql @sql;
