使用 PIVOT 和 UNPIVOT

http://technet.microsoft.com/zh-tw/library/ms177410.aspx

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 pvt1

輸出結果: