How Pivot get work in sql ? 

• Edited by Tushar Budhe 16 hours 15 minutes ago

--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)

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,