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
into #tmpTbl
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)as unpvt
GO
select * from #tmpTbl
--Pivot the table 行转列
SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM #tmpTbl
PIVOT
(
sum(Orders) FOR Employee in
(Emp1,Emp2,Emp3,Emp4,Emp5)
) as pvt
drop table #tmpTbl
drop table pvt
--Here is another example, this is better solution
SELECT *
FROM (
SELECT SectionTypeID,
SubsidiaryID,
LOBID,
CompetitorID,
FiscalYear,
FiscalMonth,
ReleaseVersion,
DataValue
FROM dbo.MarketShareImport as MS
WHERE SubsidiaryID=@SubsidiaryID
AND LOBID = @LOBID
AND FiscalYear=@FiscalYear
AND ReleaseVersion=@ReleaseVersion) TMP
PIVOT(SUM(DataValue) FOR FiscalMonth in (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,[Dec])) as pvt