透视数据实际上就是行状态转为例状态
先加一张测试表
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
GO
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
orderdate DATE NOT NULL,
empid INT NOT NULL,
custid ) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , );
SELECT * FROM dbo.Orders;
把这张表查出来
SELECT empid, custid, SUM(qty) AS sumqty FROM dbo.Orders GROUP BY empid, custid;
我们将 custid行转换成例
SELECT empid, SUM(CASE WHEN custid = 'A' THEN qty END) AS A, SUM(CASE WHEN custid = 'B' THEN qty END) AS B, SUM(CASE WHEN custid = 'C' THEN qty END) AS C, SUM(CASE WHEN custid = 'D' THEN qty END) AS D FROM dbo.Orders GROUP BY empid;
sql server 还支持一个子句用于 行转列 PIVOT 是以FROM内嵌 表表达式实现的
SELECT empid, A, B, C, D
FROM (SELECT empid, custid, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;
PIVOT （数据行） for 要转的列 IN (转那几个数据)