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

In this tutorial I demonstrate how to create a dynamic pivot query in SQL, 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

XML PATH

TYPE in FOR XML queries

value() Method

STUFF

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
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)

SET @Columns =
STUFF(
(
SELECT DISTINCT
',' + QUOTENAME(Product) AS Product
FROM dbo.Orders
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

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;'

--SELECT @Query;

EXEC sp_executesql @Query;

[houstonvanhoy]

My mind is being stretched. Great step-by-step instruction and demonstration, as usual. 👍

[tcixoed]

Excellent walkthrough of the building of the column list. The code is so cryptic the first time I read it somewhere else. The step-by-step extrication of the FOR XML PATH mode, the STUFF function, the value() method and the TYPE directive is superb. Please posting tutorials.

[briandennehy]

Lots of good stuff in this cheers Dev 👍

[bn_castillo]

How do you total all items? you just total per item

[himayanaganti]

Hi Thanks for the Video, I have some requirement, if not doing sum and Extending columns like monitor, monitor as monitor 1 (second Monitor column), monitor as monitor 2(3rd Monitor column) probably up to 10 such column's for monitor and similarly to other columns like laptop, pc, tablet (10 each). is there a way to perform such query

[mariatomova]

Hey, that video and the explanation is absolutely great! Thanks a lot!
Although everything works just fine I don’t understand why do we create the @query variable as a string :?

[quocphu]

thanks for your tutorial. But i have a problem. I can't run the code after i add the last row " EXEC sp_executesql @Query" but before. Can you help me ?

[gibsongilbert]

I want to sum up all the @columns in the product and also show a total of it. how I can do that?

[queensfinezt]

Hi great tutorial, I’m facing a slightly different situation. I’m creating a view, the problem I'm facing is that in a view I can’t declare any variables. I would like to know how to create dynamic rows that should be inside my "FOR IN()" statement. My view has a couple of CTEs but the last one is where I want my dynamic pivot. It's something like this. Thank you for your help

CREATE VIEW [MyView] AS

WITH CTE 1as(
--- Query
) ,
SELECT * From CTE2(

-- Select * From CTE1
), Select * From CTE2
PIVOT (
Max([Value])
For CodeName IN(
-- this is what I currently have hard coded
--- [HardCodeColumn 2],
--- [HardCodeColumn 3],
--- [HardCodeColumn 4],
--- [HardCodeColumn 5],



- - query or logic that should go here in order to get dynamic columns
--that will output
-- [column1]
--, [column2], …..

)
)

[maryk.larson]

Hi there - thanks for all your great content! Do you by chance have a video for doing a dynamic pivot in a version of SQL that's 2017 or later? We're on 2019 so I am intrigued after your mention of the easier functions available....

[hasanmougharbel]

Hey there, god bless your efforts in this channel.
I am new to sql learner and has a simple enquiry.
Is XML a file type like CSV or it is an integration tool?
Thanks for taking care of my enquiry.