PIVOT:
•Convert your output from (values)vertical order to horizontal(making columns) one
Topic to cover:
•PIVOT example
•PIVOT syntax
•Interview Question
•PIVOT on northwind
•Dynamic PIVOT query
SQL Query:
--SYNTAX:
--SELECT
--VALUE1 as [Alias1], VALUE1 as [Alias2]
--FROM
--(SELECT [VALUE SOURCE], [VALUE TO AGGREGATE]
-- FROM SourceTable) AS SourceTable
--PIVOT
--(
--sum([VALUE TO AGGREGATE])
--FOR [VALUE SOURCE] IN (VALUE1, VALUE2)
--) AS PivotTable;
create table #GADGET (
GADGET_CODE VARCHAR(10),
WIDGET_CODE VARCHAR(10),
NUM_AVAIL Int,
COLOR VARCHAR(10)
)
INSERT INTO #GADGET VALUES (1,'ABC',5, 'BLUE');
INSERT INTO #GADGET VALUES (2,'ABC',2, 'WHITE');
INSERT INTO #GADGET VALUES (3,'DEF',2, 'WHITE');
SELECT
SUM(CASE WHEN COLOR = 'WHITE' THEN NUM_AVAIL else 0 end)
AVAILABLE_WHITE,
SUM(CASE WHEN COLOR = 'BLUE' THEN NUM_AVAIL else 0 end)
AVAILABLE_BLUE,
SUM(CASE WHEN COLOR = 'BLUE' OR COLOR = 'WHITE' THEN NUM_AVAIL
else 0 end) AVAILABLE_TOTAL
FROM #GADGET
SELECT
[WHITE] , [Blue] ,[WHITE]+[Blue] [TOTAL AVAILABLE]
FROM
(SELECT color, #GADGET.NUM_AVAIL
FROM #GADGET) AS SourceTable
PIVOT
(
sum(NUM_AVAIL)
FOR color IN ([WHITE], [Blue])
) AS PivotTable;
drop table #GADGET
--NorthWind Example
Select productname,1 [Unit],CategoryName from Products inner join Categories
on Products.CategoryID=Categories.CategoryID
Select count(1),CategoryName from Products inner join Categories
on Products.CategoryID=Categories.CategoryID group by CategoryName
SELECT
[Beverages] , [Condiments] ,[Confections] ,[Grains/Cereals],[Produce]
FROM
(Select 1 [Unit],CategoryName from Products inner join Categories
on Products.CategoryID=Categories.CategoryID) AS SourceTable
PIVOT
(
sum([Unit])
FOR CategoryName IN ([Beverages], [Condiments],[Confections],[Grains/Cereals],[Produce])
) AS PivotTable;
Select prod.ProductName,sum(ord.Quantity) from [Order Details] ord inner join Products prod
on ord.ProductID=prod.ProductID group by prod.ProductName
SELECT [Guaraná Fantástica], [Ravioli Angelo],[Chang]
FROM (Select prod.ProductName,ord.Quantity from [Order Details] ord inner join Products prod
on ord.ProductID=prod.ProductID ) t
PIVOT(SUM(quantity)
FOR ProductName IN ([Guaraná Fantástica], [Ravioli Angelo],[Chang])) AS PVTTable
DECLARE @DynamicQuery AS NVARCHAR(MAX)
DECLARE @ColumnNameList AS VARCHAR(MAX)
--Query to get list of column in one variable i.e @ColumnNameList
SELECT @ColumnNameList= ISNULL(@ColumnNameList + ',','')
+ QUOTENAME(ProductName)
FROM (SELECT DISTINCT ProductName FROM (Select prod.ProductName,ord.Quantity
from [Order Details] ord inner join Products prod
on ord.ProductID=prod.ProductID ) t) AS Courses
--priting column list
print @ColumnNameList
--Generating dynamic PIVOT Query
SET @DynamicQuery =
N'SELECT ' + @ColumnNameList + '
FROM (Select prod.ProductName,ord.Quantity from [Order Details] ord inner join Products prod
on ord.ProductID=prod.ProductID ) t
PIVOT(SUM(Quantity)
FOR ProductName IN (' + @ColumnNameList + ')) AS PVTTable'
--Priting dynamic query
print @DynamicQuery
--Executing dynamic query
EXEC sp_executesql @DynamicQuery
Click here to download script