Dynamic pivot query in SQL

Dynamic pivot query in SQL

SELECT distinct  [ProductName]

      ,CategoryID

      ,[UnitPrice]

  FROM DynamicPivot

 

DECLARE @CategoryID NVARCHAR(MAX) = ''

DECLARE @Query NVARCHAR(MAX) = ''

 

SELECT  @CategoryID +=   QUOTENAME(CategoryID)+ ','

FROM

(

       SELECT DISTINCT CategoryID

       FROM DynamicPivot

) AS CatId

 

SET @CategoryID = LEFT(@CategoryID, LEN(@CategoryID)-1)

 

SET @Query =

'SELECT * FROM

 

(SELECT

       CategoryID,

       ProductName,

       UnitPrice

FROM

       DynamicPivot

)

AS CatIds

PIVOT(

       AVG(UnitPrice)

       FOR CategoryID IN (' + @CategoryID +')

) AS ProductPivotTable'

 

EXECUTE sp_executesql @Query

Inserting few new records in table

  insert into DynamicPivot

  values('Ravioli Angelo',9,19.50)

  insert into DynamicPivot

  values('Chai',9,19)

  insert into DynamicPivot

  values('Filo Mix',9,50)

  insert into DynamicPivot

  values('Ravioli Angelo',10,19.50)

  insert into DynamicPivot

  values('Filo Mix',10,25)

Again run above dynamic pivot query

Also try bellow queries to achieve same

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT distinct  ',' + QUOTENAME([CategoryID])

                        FROM DynamicPivot

                FOR XML PATH(''), TYPE

                ).value('.', 'NVARCHAR(MAX)')

            ,1,1,'')

 

PRINT @cols

 

SELECT @query =

    'SELECT * FROM

    (SELECT    

        CategoryID,

             ProductName,

             UnitPrice

    FROM DynamicPivot)X

    PIVOT

    (

        AVG(UnitPrice)

        for [CategoryID] in (' + @cols + ')

    ) P'

 

    EXEC SP_EXECUTESQL @query

 

/************************************************/

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

DECLARE @ColumnName AS NVARCHAR(MAX)

 

--Get distinct values of the PIVOT Column

SELECT @ColumnName= ISNULL(@ColumnName + ',','')

       + QUOTENAME(CategoryID)

FROM (SELECT DISTINCT CategoryID FROM DynamicPivot) AS Pvt

--Prepare the PIVOT query using the dynamic

SET @DynamicPivotQuery =

  'SELECT ProductName,'  + @ColumnName + '

    FROM DynamicPivot

    PIVOT(SUM(UnitPrice)

          FOR CategoryID IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery

 

/**************** Replace NULL values with Zero *****************/

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),

        @PivotColumnNames AS NVARCHAR(MAX),

        @PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column

SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')

       + QUOTENAME(CategoryID)

FROM (SELECT DISTINCT CategoryID FROM DynamicPivot) AS CategoryIDs

--Get distinct values of the PIVOT Column with isnull

SELECT @PivotSelectColumnNames

    = ISNULL(@PivotSelectColumnNames + ',','')

    + 'ISNULL(' + QUOTENAME(CategoryID) + ', 0) AS '

    + QUOTENAME(CategoryID)

FROM (SELECT DISTINCT CategoryID FROM DynamicPivot) AS CategoryIDs

--Prepare the PIVOT query using the dynamic

SET @DynamicPivotQuery =

N'SELECT ProductName, ' + @PivotSelectColumnNames + '

FROM DynamicPivot

PIVOT(SUM(UnitPrice)

FOR CategoryID IN (' + @PivotColumnNames + ')) AS PVTTable'

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery