T-SQL枢轴?根据行值创建表格列的可能性 [英] T-SQL Pivot? Possibility of creating table columns from row values
问题描述
实际上是否可以旋转T-SQL(2005),以使(出于争论目的)第一列的行的值成为输出表的列的标题?
Is it actually possible to rotate a T-SQL (2005) so that (for the sake of argument) the values of the first column's rows become the titles of the output table's columns?
我意识到这并不是PIVOT真正的目的,而是我所需要的-能够请求表格的功能,因为这些列已作为值输入到表格中,因此事先不知道这些列.
I realise this is not really what PIVOT is for, but it's what I need - the ability to request a table where the columns are not known before-hand because they have been entered as values into a table.
即使是黑客也很好,tbh.
Even a hack would be nice, tbh.
推荐答案
Itzik Ben-Gan关于如何构建动态PIVOT的示例,我强烈推荐他的
Itzik Ben-Gan's example on how to build dynamic PIVOT, I highly recommend his Inside Microsoft SQL Server 2008: T-SQL Programming book
-- Creating and Populating the Orders Table
USE tempdb;
GO
IF OBJECT_ID('dbo.Orders') IS NOT NULL
DROP TABLE dbo.Orders;
GO
CREATE TABLE dbo.Orders
(
orderid int NOT NULL PRIMARY KEY NONCLUSTERED,
orderdate datetime NOT NULL,
empid int NOT NULL,
custid varchar(5) NOT NULL,
qty int NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid
ON dbo.Orders(orderdate, orderid);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30001, '20020802', 3, 'A', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10001, '20021224', 1, 'A', 12);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10005, '20021224', 1, 'B', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40001, '20030109', 4, 'A', 40);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10006, '20030118', 1, 'C', 14);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20001, '20030212', 2, 'B', 12);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40005, '20040212', 4, 'A', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20002, '20040216', 2, 'C', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30003, '20040418', 3, 'B', 15);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30004, '20020418', 3, 'C', 22);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30007, '20020907', 3, 'D', 30);
GO
-- Static PIVOT
SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR orderyear IN([2002],[2003],[2004])) AS P;
GO
-- Dynamic PIVOT
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
DECLARE
@cols AS NVARCHAR(MAX),
@y AS INT,
@sql AS NVARCHAR(MAX);
-- Construct the column list for the IN clause
-- e.g., [2002],[2003],[2004]
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT YEAR(orderdate) AS y FROM dbo.Orders) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P;';
EXEC sp_executesql @sql;
GO
这篇关于T-SQL枢轴?根据行值创建表格列的可能性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!