Create Dynamic PIVOT Queries in SQL Server

Problem statement

Pivoting is basic or common requirement in which data is stored in a column and needs to show it in rows. PIVOT is very good feature but there is one drawback with PIVOT; it is that we need to pass all of the values that we need to pivot on. It might be possible that we don't know all the values to pivot on. In this article I am going to explain how to create a dynamic PIVOT query.

Suppose I have a table called Order Master. This table contains Order Id, Order Date and Order Amount columns. Now if I want to make a report that shows dates as columns and total amounts of those dates as rows using a SQL PIVOT query.

The following is the TSQL for creating an Order Master Table and inserting some dummy data into the table:

CREATE TABLE [dbo].[OrderMaster](
[OrderId] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [date] NULL,
[OrderAmount] [money] NULL,
CONSTRAINT [PK_OrderMaster] PRIMARY KEY CLUSTERED 
(
[OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO OrderMaster VALUES('2014-05-01',3500),
('2014-05-02',3000),
('2014-05-03',2800),
('2014-05-04',4500),
('2014-05-05',350)

Required output

Solution

We can write a hard-coded PIVOT if we know all the possible values that need to pivoted on. Using the following procedure we can write a PIVOT query dynamically.

Step 1. Declaring required variables

DECLARE @columnscsv VARCHAR(MAX)
DECLARE @Sumcolumnscsv VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

Step 2. PIVOT query required column that contains the values that will become the column header. The following query helps us to create a column name string dynamically.

SELECT @columnscsv = COALESCE(@columnscsv + '],[','') + CAST(orderDate as VARCHAR(10))
 FROM OrderMaster
 GROUP BY OrderDate
SET @columnscsv = '[' + @columnscsv + ']'
print @columnscsv

The following is the output of the preceding query:

Step 3. The following query helps us to create an output columns name string with summation:

SELECT @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + 'sum(isnull([','') + CAST(orderDate as VARCHAR(10)) + '],0)) as [' + CAST(orderDate as VARCHAR(10)) + '],'
FROM OrderMaster
GROUP BY OrderDate
SET @Sumcolumnscsv =  'sum(isnull(['  +  LEFT(@Sumcolumnscsv, LEN(@Sumcolumnscsv) - 1)  
print @Sumcolumnscsv

The following is the output of the preceding query:

Step 4. Writing Final query

SET @sql = 'SELECT  ''Total Amount'' as Date, ' + @Sumcolumnscsv + ' FROM OrderMaster ' +
   ' PIVOT ' +
   ' ( ' +
   ' sum(OrderAmount)' +
   ' FOR OrderDate IN (' + @columnscsv + ') )  AS PivotTable'
EXEC (@sql)

Final Output

Summary

I hope this article may help you to create a dynamic PIVOT query.