SQL Tutorial - How to create a Dynamic Pivot in SQL Part 2 

In this tutorial I demonstrate how to create a dynamic pivot query in SQL using STRING_AGG, a function added in SQL Server 2017. Performing pivot operations in SQL can be quite static when we have to list out each attribute and building a dynamic pivot query can help us solve that problem. Dynamic pivot queries also have the added benefit that we don't need to change the query when new values are added like we would have to do with a static pivot query.
This video may introduce you to a number of functions and concepts that are new to you, further reading is available on Microsoft Docs:
QUOTENAME
docs.microsoft...
STRING_AGG
docs.microsoft...
If you would like to follow along with the video, the scripts below can be used:
/* SETUP */
-- check if table exists
IF OBJECT_ID(N'dbo.Orders', N'U') IS NOT NULL
DROP TABLE dbo.Orders;
-- create table
CREATE TABLE dbo.Orders
(
OrderId INT IDENTITY(1, 1)
CONSTRAINT PK_Orders_OrderID PRIMARY KEY (OrderId),
CustomerId INT,
Product VARCHAR(50),
Amount DECIMAL(6, 2)
);
-- set variables
DECLARE
@Customers INT = 1000,
@Products INT = 8;
WITH
L0 AS (SELECT 1 AS N UNION ALL SELECT 1),
L1 AS (SELECT 1 AS N FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS N FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS N FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS N FROM L3 AS A CROSS JOIN L3 AS B)
INSERT INTO dbo.Orders (CustomerId, Product, Amount)
SELECT
CustomerId,
CASE ProductID
WHEN 1 THEN 'Monitor'
WHEN 2 THEN 'Laptop'
WHEN 3 THEN 'PC'
WHEN 4 THEN 'Tablet'
WHEN 5 THEN 'Mobile Phone'
WHEN 6 THEN 'Printer'
WHEN 7 THEN 'Scanner'
WHEN 8 THEN 'Headset'
END AS Product,
Amount
FROM
(
SELECT
CustomerId,
CEILING(ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY (SELECT NULL)) / 9.00) AS ProductId,
Amount
FROM
(
SELECT
NTILE(@Customers) OVER(ORDER BY (SELECT NULL)) AS CustomerId,
CAST(ABS(CHECKSUM(NEWID())) / 1000000.00 AS DECIMAL(6, 2)) AS Amount
FROM L4
) AS D
) AS D2;
SELECT
*
FROM dbo.Orders;
/* Original Pivot Query */
SELECT
CustomerId,
[Monitor],
[Laptop],
[PC],
[Tablet],
[Mobile Phone],
[Printer],
[Scanner],
[Headset]
FROM
(
SELECT
CustomerId,
Product,
Amount
FROM dbo.Orders
) AS D
PIVOT
(
SUM(Amount) FOR [Product] IN ([Monitor], [Laptop], [PC], [Tablet], [Mobile Phone], [Printer], [Scanner], [Headset])
) AS P
ORDER BY CustomerId;
/* Add an additional product */
INSERT INTO dbo.Orders (CustomerId, Product, Amount)
VALUES
(1, 'Television', 600.00),
(2, 'Television', 600.00),
(3, 'Television', 600.00);
/* Dynamic Pivot Query */
DECLARE
@Query NVARCHAR(MAX),
@Columns NVARCHAR(MAX)
SELECT @Columns =
STRING_AGG(Product, ',')
FROM
(
SELECT DISTINCT
QUOTENAME(Product) AS Product
FROM dbo.Orders
) AS D;
SET @Query =
'SELECT
CustomerId, ' +
@Columns +
'
FROM
(
SELECT
CustomerId,
Product,
Amount
FROM dbo.Orders
) AS D
PIVOT
(
SUM(Amount) FOR [Product] IN (' + @Columns + ')
) AS P
ORDER BY CustomerId'
EXEC sp_executesql @Query;