(SQL SERVER) PIVOT WITH DYNAMIC COLUMNS

Pivot is generally used in creating reports that converts table row values into column names. There are two ways of creating a pivot table. One way is to manually specify the column names that we want and create the desired output. Let me explain it with an example table named Products which when executed creates an output,

                         

Now, we need to create a table that displays the ProductName values into column names and prices of that particular product as the row values. So the query to be executed is,

SELECT [Milk],[Fresh Juice], [Horlicks], [Boost], [Complan] FROM

(SELECT ProductName, Price FROM dbo.Products) prod

PIVOT (SUM(price) FOR prod.ProductName

IN ([Milk],[Fresh Juice], [Horlicks], [Boost], [Complan])) AS pvt

GROUP BY [Milk],[Fresh Juice], [Horlicks], [Boost], [Complan]

The output for above executed query will be,

This will be useful when we want to create a table with a desired name of columns. Another way to create a pivot table is by using dynamic columns i.e., all the values of particular column should be converted into column names. Let’s create it by using two different methods.

First Method,

DECLARE @cols NVARCHAR(2000)                         

DECLARE @output VARCHAR(MAX)

SELECT @cols = COALESCE(@cols + ',[' + ProductName + ']','[' + ProductName + ']')

FROM dbo.Products

SET @output = N'SELECT ' + @cols + ' FROM

(SELECT ProductName, Price FROM dbo.Products) prod

PIVOT (SUM(price) FOR prod.ProductName

IN ( ' + @cols + ' )) AS pvt'

EXEC (@output)

Executing the above query produces the output,

This method uses coalesce to generate the comma separated values of ProductName column and assigns the price value to the particular column.

Second Method,

DECLARE Products CURSOR FOR

SELECT ProductName FROM dbo.Products

DECLARE @ProductName NVARCHAR(MAX),

           @Prod VARCHAR(50),

           @Output NVARCHAR(MAX)

SET @Prod = ''

OPEN Products

FETCH NEXT FROM Products INTO @ProductName

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @Prod = @Prod + '[' + @ProductName + '],'

 FETCH NEXT FROM Products INTO @ProductName

END

CLOSE Products

DEALLOCATE Products

SET @Prod = SUBSTRING(@Prod, 0, LEN(@Prod))

SET @Output = 'SELECT ' + @Prod + ' FROM

(SELECT ProductName, Price FROM dbo.Products) prod

PIVOT

(SUM(Price) for ProductName in ('+ @Prod +')) AS pvt'

PRINT @Output

EXEC sp_executesql @Output

Executing the above query produces the same output as the first method. This method uses CURSOR to generate the comma separated values of ProductName column. Hope this helps. :)