脂溢性脱发咨询微信moka818,网店信誉,交警岗亭
透视数据实际上就是行状态转为例状态
先加一张测试表
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 VARCHAR(5) NOT NULL, qty INT NOT NULL, CONSTRAINT PK_Orders PRIMARY KEY(orderid) ); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES (30001, '20070802', 3, 'A', 10), (10001, '20071224', 2, 'A', 12), (10005, '20071224', 1, 'B', 20), (40001, '20080109', 2, 'A', 40), (10006, '20080118', 1, 'C', 14), (20001, '20080212', 2, 'B', 12), (40005, '20090212', 3, 'A', 10), (20002, '20090216', 1, 'C', 20), (30003, '20090418', 2, 'B', 15), (30004, '20070418', 3, 'C', 22), (30007, '20090907', 3, 'D', 30); 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 (转那几个数据)
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论