Problem

In my article Working with PIVOT, I introduced a simple way to implement PIVOT table in SQL Server. However, the data set is static and if we insert more data, the PIVOT query will not work. For example, there is a Sales table as below

Product             Date                                 Qty

A                        03/11/2017                     1

A                        03/11/2017                     1

B                        03/12/2017                     1

C                        03/11/2017                      1

C                        03/13/2017                      1

The PIVOT query below and the result returns correctly

SELECT Product,[03/11/2017],[03/12/2017],[03/13/2017]
FROM Sales s
PIVOT
(
SUM(Qty)
FOR Date IN ([03/11/2017],[03/12/2017],[03/13/2017])
)pv;

Product             03/11/2017                 03/12/2017                         03/13/2017
A                         3                                    NULL                                   NULL
B                         NULL                           1                                            NULL
C                         1                                     NULL                                   1

However, the above PIVOT does not wok well if we insert more data into Sales table such D product and Date 03/14/2017

Solution

To solve this issue, we need to use dynamic PIVOT table as below

  • Create a Sales table

CREATE TABLE Sales
(
Product varchar(50),
Date varchar(20),
Qty int
)
GO
INSERT INTO Sales
VALUES(‘A’,’03/11/2017′,1),
(‘A’,’03/11/2017′,1),
(‘A’,’03/11/2017′,1),
(‘B’,’03/12/2017′,1),
(‘C’,’03/13/2017′,1),
(‘C’,’03/11/2017′,1)
(‘D’,’03/14/2017′,1)

  • Build a dynamic PIVOT table

DECLARE @DynamicPivotQuery AS NVARCHAR(500)
DECLARE @ColumnName AS NVARCHAR(500)
DECLARE @ColumnNameISNULL AS NVARCHAR(500)

— Prepare columns for PIVOT
SELECT @ColumnName= ISNULL(@ColumnName + ‘,’,”)
+QUOTENAME(Date)
FROM (SELECT DISTINCT Date FROM Sales) AS s

— In case of NULL, we need to check and convert to 0

SELECT @ColumnNameISNULL= ISNULL(@ColumnNameISNULL + ‘,’,”)
+’ISNULL(‘+ QUOTENAME(Date)+’,0) AS ‘+QUOTENAME(Date)
FROM (SELECT DISTINCT Date FROM Sales) AS s

SET @DynamicPivotQuery =
N’SELECT Product, ‘ + @ColumnNameISNULL + ‘
FROM Sales
PIVOT(SUM(Qty)
FOR Date IN (‘ + @ColumnName + ‘)) AS PVTTable’

PRINT (@DynamicPivotQuery)
EXEC sp_executesql @DynamicPivotQuery

The result is correctly as below and we can insert more and more data with Product and Date. The dynamic PIVOT still return what we expect.

Product             03/11/2017           03/12/2017             03/13/2017               03/14/2017
A                         3                             0                                0                                   0
B                         0                             1                                 0                                   0
C                         1                              0                                1                                    0
D                        0                              0                                0                                    1

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s