TechSapphire Tuesday, 2024-03-19, 11:27 AM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • 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

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2024