SQL Tutorial - How to create a Dynamic Pivot in SQL Part 1
HTML-код
- Опубликовано: 24 июл 2022
- 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
docs.microsoft.com/en-us/sql/...
XML PATH
docs.microsoft.com/en-us/sql/...
TYPE in FOR XML queries
docs.microsoft.com/en-us/sql/...
value() Method
docs.microsoft.com/en-us/sql/...
STUFF
docs.microsoft.com/en-us/sql/...
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)
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; 
Наука
My mind is being stretched. Great step-by-step instruction and demonstration, as usual. 👍
Brad: Do you encounter XQUERY often in your work?
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.
Thanks so much 👍, I will be back soon.
Lots of good stuff in this cheers Dev 👍
Thanks Brian
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 :?
We need to use a valid data type for our parameter, in this case we are building the query, something of variable length that contains text. NVARCHAR(MAX) is overkill in this scenario.
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.
XML is similar to CSV in that both are information/data formats. CSV is simple and flat whereas XML is flexible and supports nesting. Both can be used for data integration.
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
Hi Hima, apologies, I don't understand what you are trying to achieve and don't really want to guess, can you give me a bit more detail of what your data looks like and what you want it to look like, if its easier you can reach out to me at askbeardeddev@gmail.com.
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....
Sure, I cover a simpler version in part 2, ruclips.net/video/n9d8WSEBzAY/видео.html
@@BeardedDevData - thank you! I ended up watching that yesterday and saw it was covered there. I've used STRING_AGG before - a very handy function. I have a question about it, which I'll post on the other video. But a follow up question for this video -- when inserting a pivot table inside a larger query (i.e., among other CTEs), does the pivot need to happen at the very bottom, in the main query? Can one call the @Columns into the main query?
Oh - another question - if you want to replace the NULLs in the pivoted results with zeros, how (ISNULL? COALESCE? Something else?) and where is it best to do that?
That can be done after the PIVOT within the outer SELECT, technically you could do it before but if data doesn't exist, you would need to insert it, ISNULL or COALESCE will both work fine.
That's difficult to answer because it depends on your needs, you could do some data prep for example then perform a pivot operation on that data or you may need to do the pivot operation first then do some cleaning, filtering or maybe a combination, in terms of @Columns can use that anywhere, doesn't have to be pivot as long as its dynamic SQL.
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 ?
Sure, can you share the error message you are getting and the RDBMS you are using
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],…..
)
)
As you can't declare variables within a view, you would need to create either a multi-valued table value function or a stored procedure.
I want to sum up all the @columns in the product and also show a total of it. how I can do that?
Hi Gibson, check out this video, should cover what you need, ruclips.net/video/YPZwnb1WX9I/видео.html
𝐩𝓻Ỗ𝓂Ø𝓈Ｍ 😥