Dynamic Pivot Query in SQL Server

• Download source - 1.2 KB

Introduction

Pivot query is a simple way to transform your row level data into columns. T-SQL developers generally need pivot data for reporting purposes.

This tip will guide you to use pivot and dynamic pivot query in a simple step-by-step way. The purpose is to keep it simple to get you started and you can take it from there.

Using the Code

Let's define a simple statistics table which holds sales for each month. Let's name it #REVENUE.

CREATE TABLE #REVENUE
(
ID             INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[MONTH]        VARCHAR(8) NOT NULL,
SALES          DECIMAL(8,2) NOT NULL
)

Now, proceeding further, let's add some data in our table.

INSERT INTO #REVENUE
([MONTH],SALES)
VALUES
('JAN-2015', 200000.16),
('FEB-2015', 220000.17),
('MAR-2015', 227000.55),
('APR-2015', 247032.75),
('MAY-2015', 287652.75),
('JUN-2015', 265756.75)

Now, say, you need a report in which you need to show data of each month row-wise. For this, you may use pivot query something like this:

SELECT * FROM
(SELECT     
    [MONTH], 
    SALES
FROM #REVENUE)X
PIVOT 
(
    AVG(SALES)
    for [MONTH] in ([JAN-2015],[FEB-2015],[MAR-2015],[APR-2015],[MAY-2015],[JUN-2015])
) P

This query will show the following results:

JAN-2015       FEB-2015       MAR-2015       APR-2015       MAY-2015       JUN-2015
200000.160000  220000.170000  227000.550000  247032.750000  287652.750000  265756.750000

The problem with the above query is that it has static colums. It will lose purpose as soon as data for the next month will get inserted.
Let's insert data for the next month.

INSERT INTO #REVENUE
([MONTH],SALES)
VALUES
('JUL-2015', 316532.16)

To solve the above problem, we need to build dynamic SQL which picks all the month dynamically from a table. Here it goes:

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

SELECT @cols = STUFF((SELECT  ',' + QUOTENAME([MONTH]) 
                    FROM #REVENUE
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SELECT @query = 
'SELECT * FROM
(SELECT     
    [MONTH], 
    SALES
FROM #REVENUE)X
PIVOT 
(
    AVG(SALES)
    for [MONTH] in (' + @cols + ')
) P'

EXEC SP_EXECUTESQL @query

This query will show the following results:

JAN-2015       FEB-2015       MAR-2015       APR-2015       MAY-2015       JUN-2015       JUL-2015
200000.160000  220000.170000  227000.550000  247032.750000  287652.750000  265756.750000  316532.16

Lets wrap everything in a SP, so that it can be binded with a report.
SP has one argument for month (default null). If user wants, he can view all the data else he can view data of a particular month.

IF EXISTS(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'DynamicPivot')
BEGIN
    DROP PROCEDURE DynamicPivot
END
GO

CREATE PROCEDURE [dbo].[DynamicPivot]
    @Month                VARCHAR(8) = NULL
AS
BEGIN

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

    SELECT @cols = STUFF((SELECT  ',' + QUOTENAME([MONTH]) 
                        FROM #REVENUE WHERE (@Month IS NULL) OR ([MONTH] = @Month)
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

    PRINT @cols

    SELECT @query = 
    'SELECT * FROM
    (SELECT     
        [MONTH], 
        SALES
    FROM #REVENUE)X
    PIVOT 
    (
        AVG(SALES)
        for [MONTH] in (' + @cols + ')
    ) P'

    EXEC SP_EXECUTESQL @query

END
GO

Points of Interest

This is a very simple implementation of dynamic pivot query so that you can get the point and build your complex logic after understanding the concept.

Thanks for reading. Hope it helped you.