Facebook

•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

Leave a Reply

Your email address will not be published. Required fields are marked *