PIVOT行转列,UNPIVOT列转行
程序员文章站
2024-02-16 00:01:34
...
PIVOT: 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。 UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。 无 USE AdventureWorks2008R2;GOSELECT VendorID, [250]
PIVOT:通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。
UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。
USE AdventureWorks2008R2; GO SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [250], [251], [256], [257], [260] ) ) AS pvt ORDER BY pvt.VendorID;
--Create the table and insert values as portrayed in the previous example. CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int); GO INSERT INTO pvt VALUES (1,4,3,5,4,4); INSERT INTO pvt VALUES (2,4,1,5,5,5); INSERT INTO pvt VALUES (3,4,3,5,4,4); INSERT INTO pvt VALUES (4,4,2,5,5,4); INSERT INTO pvt VALUES (5,5,1,5,5,5); GO --Unpivot the table. SELECT VendorID, Employee, Orders FROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p UNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) )AS unpvt; GO
create?table?test(id?int,name?varchar(20),quarter?int,profile?int)? insert?into?test?values(1,'a',1,1000) insert?into?test?values(1,'a',2,2000) insert?into?test?values(1,'a',3,4000) insert?into?test?values(1,'a',4,5000) insert?into?test?values(2,'b',1,3000) insert?into?test?values(2,'b',2,3500) insert?into?test?values(2,'b',3,4200) insert?into?test?values(2,'b',4,5500) select?*?from?test --行转列 select?id,name, [1]?as?"一季度", [2]?as?"二季度", [3]?as?"三季度", [4]?as?"四季度", [5]?as?"5" from test pivot ( sum(profile) for?quarter?in ([1],[2],[3],[4],[5]) ) as?pvt create?table?test2(id?int,name?varchar(20),?Q1?int,?Q2?int,?Q3?int,?Q4?int) insert?into?test2?values(1,'a',1000,2000,4000,5000) insert?into?test2?values(2,'b',3000,3500,4200,5500) select?*?from?test2 --列转行 select?id,name,quarter,profile from test2 unpivot ( profile for?quarter?in ([Q1],[Q2],[Q3],[Q4]) )? as?unpvt