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;

[@getvdp]

Brad, really appreciate you taking time and sharing your knowledge, your videos are really helpful.

[@BeardedDevData]

No problem, glad that you find them useful.

[@houstonvanhoy7767]

What a relief! This is like going from straight drive to automatic. 😊

[@singuyen5927]

Lately I’ve been using a lot of stringagg of a distinct list. Making it dynamic like how you showed will be another great tool in the bag. Thank you so much!

[@BeardedDevData]

Excellent.

[@houstonvanhoy7767]

Brad: Your LinkedIn profile shows an impressive list of Microsoft certifications.
When you have time, please do a video on what SQL Server certifications are still valid and worthwhile, and what to leave behind at this point in time - early 2023. Thanks.

[@BeardedDevData]

I do need to do some videos on certifications, it's something I have invested a lot of time into.

[@maryk.larson8555]

Another great, incredibly helpful, easy to follow video - thank you! Question about STRING_AGG: I and my colleagues occasionally run into circumstances where the list it brings back is non-unique - i.e., there are duplicates in it, inexplicably; we cannot get STRING_AGG to work and must result to using STUFF (yuck). This doesn't seem to happen if it's used in a CTE or main query that calls data from a preceding CTE, (or apparently a derived table, like you use here). Is there a general rule of thumb for avoiding duplicates in the aggregated string when using STRING_AGG? (Perhaps you've covered this already in another video?)

[@BeardedDevData]

Hi, you are correct, as far as I'm aware STRING_AGG will include duplicates and they need to be handled separately.

[@aunad___]

Hey Brad! Thank you for this. My code works, but now I want to save the result as a view. how do i do that?

[@BeardedDevData]

Unfortunately you wouldn't be able to run this code within a View because it's a dynamic query, you need to use a Stored Procedure.

[@beegoodstewards]

thanks buddy!

[@maulishriagrawal1875]

Can Null be removed from the output value. i have currently hardcoded IsNull along with cols name. Can we make dynamic query including IsNull??

[@BeardedDevData]

I think it could work, you can't remove NULLs though, only replace.

[@nimeshkotadia6526]

How to order by the dynamic list of columns?

[@BeardedDevData]

You can just add the column list to the ORDER BY, ORDER BY CustomerId,' + @Columns or you can actually hard code some values if you only want to order by some of the columns