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

Web Reference: Which of these queries is the faster? NOT EXISTS: SELECT ProductID, ProductName FROM Northwind..Products p WHERE NOT EXISTS ( SELECT 1 FROM Northwind..[Order Details] od WHERE p. SQL是一种用于处理数据的语言，就像我们说的汉语、英语一样，有特定的语法结构，让我们灵活地处理数据。 SQL并不难学，首先得理解 S Q L 三个字母分别代表什么。 Structured Query Language，简写为SQL，意思是结构化查询语言。也就是说SQL是用来查询数据用的，通过代码指令来实现个性化的数据抽取 ... 了解SQL的大致情况后，我们再来看看如何学？ SQL是所有数据库查询的语言，sql由于本身结构化的特点，非常容易入手。 针对不同的数据库，如hivesql、mysql、sqlserver、oracle等，sql语法会有所不同，但是总体上大同小异，只是细微处的差别。

YouTube Excerpt: 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 https://docs.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql?view=sql-server-ver16 XML PATH https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-path-mode?view=sql-server-ver16 TYPE in FOR XML queries https://docs.microsoft.com/en-us/sql/relational-databases/xml/type-directive-in-for-xml-queries?view=sql-server-ver16 value() Method https://docs.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-ver16 STUFF https://docs.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16 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;

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

Read Full Article 🔍

Curious about SQL Tutorial - How To Create A Dynamic Pivot In SQL Part 1's Color? Explore detailed estimates, salary breakdowns, and financial insights that reveal the true scope of their profile.

Source ID: SzYHiuSy0ZU

Category: color style guide