22,209
社区成员
发帖
与我相关
我的任务
分享
例子
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
SELECT * FROM #pvt
--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
/*
VendorID Employee Orders
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
3 Emp1 4
3 Emp2 3
3 Emp3 5
3 Emp4 4
3 Emp5 4
4 Emp1 4
4 Emp2 2
4 Emp3 5
4 Emp4 5
4 Emp5 4
5 Emp1 5
5 Emp2 1
5 Emp3 5
5 Emp4 5
5 Emp5 5
*/