locked
Pivot in SQL Or Dynamic Pivot RRS feed

Answers

  • --This may be helpful.

    --Download AdventureWorksLT2012 DB from MSDN restore it to SQL

    --Execute this query and analyze details. This may helpful.
    There are 3 steps :

    1> Select Column 2> Select column need to Pivot 3> Actual Pivoting

    Below is example for dynamic pivoting.

    --------------------------------------------------------------------------------------------------------

    USE AdventureWorksLT2012
    GO
    DECLARE @ColumnNameQuote Varchar(MAX)
    DECLARE @ColumnNameDeQuote Varchar(MAX)
    SET @ColumnNameQuote =''
    SELECT  @ColumnNameQuote += QUOTENAME([ProductNumber]) + ','  FROM [SalesLT].[Product] 
    SET @ColumnNameQuote = LEFT(@ColumnNameQuote,LEN(@ColumnNameQuote) -1)  
    --SELECT @ColumnNameQuote
    SET @ColumnNameDeQuote = Replace (@ColumnNameQuote,'[','''')
    SET @ColumnNameDeQuote = REPLACE (@ColumnNameDeQuote,']','''')
    --SELECT @ColumnNameDeQuote

    DECLARE @SQL1 VARCHAR(MAX)
    DECLARE  @SQL2 VARCHAR(MAX)
    SET  @SQL1 = '
    SELECT [SalesOrderID],'+@ColumnNameQuote+'  FROM 
    (
    SELECT [SalesOrderID]
          ,[OrderQty]
          ,Product.ProductNumber
      FROM [SalesLT].[SalesOrderDetail] [OrderDetails] INNER JOIN SalesLT.Product [Product]
      ON Product.ProductID = OrderDetails.ProductID WHERE  [Product].ProductNumber IN ('+@ColumnNameDeQuote+')'
      --SELECT @SQL1
      SET @SQL2 = '
      ) AS Pivoting

      PIVOT
      ( AVG([OrderQty]) FOR ProductNumber IN ('+@ColumnNameQuote+')) AS Pivoted'

        --SELECT  @SQL2

    EXECUTE (@SQL1+@SQL2)

    Tuesday, July 7, 2015 2:50 PM
  • How Pivot get work in sql ? 

    Hi Tushar,

    This is SQL Server Power Pivot forum, if you need to know how Pivot function works, you can refer to the link below which describes this function in details.
    http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
    Or you can post it on the Transact-SQL forum
    http://social.msdn.microsoft.com/Forums/en-US/transactsql

    For the Power Pivot, it is a free add-in to the 2010 version of the spreadsheet application Microsoft Excel. It extends the capabilities of the pivot table data summarization and cross-tabulation feature with new features such as expanded data capacity, advanced calculations, ability to import data from multiple sources, and the ability to publish the workbooks as interactive web applications. As such, Power Pivot falls under Microsoft's Business Intelligence offering, complementing it with its self-service, in-memory capabilities.
    http://www.powerpivotpro.com/what-is-powerpivot/

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Monday, July 20, 2015 7:33 AM
    • Marked as answer by Charlie Liao Tuesday, August 4, 2015 7:52 AM
    Wednesday, July 8, 2015 7:01 AM

All replies

  • --This may be helpful.

    --Download AdventureWorksLT2012 DB from MSDN restore it to SQL

    --Execute this query and analyze details. This may helpful.
    There are 3 steps :

    1> Select Column 2> Select column need to Pivot 3> Actual Pivoting

    Below is example for dynamic pivoting.

    --------------------------------------------------------------------------------------------------------

    USE AdventureWorksLT2012
    GO
    DECLARE @ColumnNameQuote Varchar(MAX)
    DECLARE @ColumnNameDeQuote Varchar(MAX)
    SET @ColumnNameQuote =''
    SELECT  @ColumnNameQuote += QUOTENAME([ProductNumber]) + ','  FROM [SalesLT].[Product] 
    SET @ColumnNameQuote = LEFT(@ColumnNameQuote,LEN(@ColumnNameQuote) -1)  
    --SELECT @ColumnNameQuote
    SET @ColumnNameDeQuote = Replace (@ColumnNameQuote,'[','''')
    SET @ColumnNameDeQuote = REPLACE (@ColumnNameDeQuote,']','''')
    --SELECT @ColumnNameDeQuote

    DECLARE @SQL1 VARCHAR(MAX)
    DECLARE  @SQL2 VARCHAR(MAX)
    SET  @SQL1 = '
    SELECT [SalesOrderID],'+@ColumnNameQuote+'  FROM 
    (
    SELECT [SalesOrderID]
          ,[OrderQty]
          ,Product.ProductNumber
      FROM [SalesLT].[SalesOrderDetail] [OrderDetails] INNER JOIN SalesLT.Product [Product]
      ON Product.ProductID = OrderDetails.ProductID WHERE  [Product].ProductNumber IN ('+@ColumnNameDeQuote+')'
      --SELECT @SQL1
      SET @SQL2 = '
      ) AS Pivoting

      PIVOT
      ( AVG([OrderQty]) FOR ProductNumber IN ('+@ColumnNameQuote+')) AS Pivoted'

        --SELECT  @SQL2

    EXECUTE (@SQL1+@SQL2)

    Tuesday, July 7, 2015 2:50 PM
  • How Pivot get work in sql ? 

    Hi Tushar,

    This is SQL Server Power Pivot forum, if you need to know how Pivot function works, you can refer to the link below which describes this function in details.
    http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
    Or you can post it on the Transact-SQL forum
    http://social.msdn.microsoft.com/Forums/en-US/transactsql

    For the Power Pivot, it is a free add-in to the 2010 version of the spreadsheet application Microsoft Excel. It extends the capabilities of the pivot table data summarization and cross-tabulation feature with new features such as expanded data capacity, advanced calculations, ability to import data from multiple sources, and the ability to publish the workbooks as interactive web applications. As such, Power Pivot falls under Microsoft's Business Intelligence offering, complementing it with its self-service, in-memory capabilities.
    http://www.powerpivotpro.com/what-is-powerpivot/

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Monday, July 20, 2015 7:33 AM
    • Marked as answer by Charlie Liao Tuesday, August 4, 2015 7:52 AM
    Wednesday, July 8, 2015 7:01 AM