Berikut adalah cara penggunaan Pivot pada SQL Server :
Format :
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
1. Basic PIVOT Example
Contoh 1 :
Query :
USE AdventureWorks2014 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Hasil :
DaysToManufacture AverageCost
----------------- -----------
0 5.0885
1 223.88
2 359.1082
4 949.4105
Query Pivot :
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(
SELECT DaysToManufacture, StandardCost
FROM Production.Product
) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Hasil :
Cost_Sorted_By_Production_Days 0 1 2 3 4
------------------------------ ----------- ----------- ----------- ----------- -----------
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Contoh 2 :
Query tanpa Pivot :
SELECT
[Group],
SUM([SalesYTD]) SalesYTD
FROM [Sales].[SalesTerritory]
GROUP BY [Group]
Hasil :
Query Pivot :
SELECT 'SalesYTD' AS SalesYTD, [Europe], [North America], [Pacific]
FROM
(
SELECT SalesYTD, [Group]
FROM [Sales].[SalesTerritory]
) AS TableToPivot
PIVOT
(
SUM(SalesYTD)
FOR [Group] IN ([Europe], [North America], [Pacific])
) AS PivotTable;
Hasil :
2. Query Pivot Complex
USE AdventureWorks2014;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
Hasil :
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
----------- ----------- ----------- ----------- ----------- -----------
1492 2 5 4 4 4
1494 2 5 4 5 4
1496 2 4 4 5 5
1498 2 5 4 4 4
1500 3 4 4 5 4
Contoh 1.2 :
1.2.1. Query tanpa Pivot
SELECT YEAR(SOH.OrderDate) AS SalesYear,
MONTH(SOH.OrderDate) AS SalesMonth,
SUM(SOH.SubTotal) AS TotalSales
FROM sales.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
GROUP BY YEAR(SOH.OrderDate),
MONTH(SOH.OrderDate)
ORDER BY YEAR(SOH.OrderDate),
MONTH(SOH.OrderDate);
Hasil :
1.2.2. Query Pivot
Contoh 1 :
SELECT SalesYear,
ISNULL([Q1], 0) AS Q1,
ISNULL([Q2], 0) AS Q2,
ISNULL([Q3], 0) AS Q3,
ISNULL([Q4], 0) AS Q4,
(ISNULL([Q1], 0) + ISNULL([Q2], 0) + ISNULL([Q3], 0) + ISNULL([Q4], 0)) SalesYTD
FROM
(
SELECT YEAR(SOH.OrderDate) AS SalesYear,
CAST('Q'+CAST(DATEPART(QUARTER, SOH.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)) Quarters,
SOH.SubTotal AS TotalSales
FROM sales.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) AS Data PIVOT(SUM(TotalSales) FOR Quarters IN([Q1],
[Q2],
[Q3],
[Q4])) AS pvt
ORDER BY SalesYear;
Hasil :
Contoh 2 :
SELECT SalesYear,
ISNULL([1], 0) AS Jan,
ISNULL([2], 0) AS Feb,
ISNULL([3], 0) AS Mar,
ISNULL([4], 0) AS Apr,
ISNULL([5], 0) AS May,
ISNULL([6], 0) AS Jun,
ISNULL([7], 0) AS Jul,
ISNULL([8], 0) AS Aug,
ISNULL([9], 0) AS Sep,
ISNULL([10], 0) AS Oct,
ISNULL([11], 0) AS Nov,
ISNULL([12], 0) AS Dec,
(ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0) + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0) + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0)) SalesYTD
FROM
(
SELECT YEAR(SOH.OrderDate) AS SalesYear,
DATEPART(MONTH, SOH.OrderDate) Months,
SOH.SubTotal AS TotalSales
FROM sales.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) AS Data PIVOT(SUM(TotalSales) FOR Months IN([1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12])) AS pvt;
Hasil :
Contoh 3 :
DECLARE @dml AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(Months)
FROM (SELECT DISTINCT DATEPART(MONTH, SOH.OrderDate) Months
FROM sales.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
GROUP BY YEAR(SOH.OrderDate),
DATEPART(MONTH, SOH.OrderDate)) AS Months
--Prepare the PIVOT query using the dynamic
SET @dml =
N'SELECT SalesYear, ' +@ColumnName + ' FROM
(
SELECT YEAR(SOH.OrderDate) AS SalesYear,
DATEPART(MONTH, SOH.OrderDate) Months,
SUM(SOH.SubTotal) AS TotalSales
FROM sales.SalesOrderHeader SOH
JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
GROUP BY YEAR(SOH.OrderDate),
DATEPART(MONTH, SOH.OrderDate)) AS T
PIVOT(SUM(TotalSales)
FOR Months IN (' + @ColumnName + ')) AS PVTTable'
--Print @DynamicPivotQuery
--Execute the Dynamic Pivot Query
EXEC sp_executesql @dml
Hasil :
