PIVOT: 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。 UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。 无 USE AdventureWorks2008R2;GOSELECT VendorID, [250]
PIVOT:通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。
UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。
<style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms
- –> <!—ecms
- $velocityCount
–> <!—ecms
–>
USE AdventureWorks2008R2;GOSELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) pPIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( [250], [251], [256], [257], [260] )) AS pvtORDER 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);GOINSERT 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, OrdersFROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) pUNPIVOT (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"fromtestpivot(sum(profile)for?quarter?in([1],[2],[3],[4],[5]))as?pvtcreate?table?test2(id?int,<i style="color:transparent">本文来源gaodai$ma#com搞$代*码6网</i>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,profilefromtest2unpivot(profilefor?quarter?in([Q1],[Q2],[Q3],[Q4]))?as?unpvt
转载请注明原文链接：PIVOT行转列，UNPIVOT列转行
