PIVOT

  • 200
  • 0
  • 2020-01-28

PIVOT,資料橫轉正

DROP TABLE #pvt

CREATE TABLE #pvt (VendorID varchar(10), Emp1 int);
GO
INSERT INTO #pvt VALUES ('A121',4121);
INSERT INTO #pvt VALUES ('A121',4345);
INSERT INTO #pvt VALUES ('A121',4454);
INSERT INTO #pvt VALUES ('A122',1234);
INSERT INTO #pvt VALUES ('A122',1121);
INSERT INTO #pvt VALUES ('A123',5232);

select * from #pvt

SELECT *
FROM 
(SELECT VendorID,Emp1
	,ROW_NUMBER() OVER(partition by VendorID order by VendorID) RID
FROM #pvt) p
PIVOT
(
MAX(Emp1)
FOR RID IN --用RID對應要塞入的欄位
( [1], [2], [3])
) AS pvt