首頁 >資料庫 >mysql教程 >PIVOT行转列,UNPIVOT列转行

PIVOT行转列,UNPIVOT列转行

WBOY
WBOY原創
2016-06-07 14:56:251405瀏覽

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
陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn