SQL Server 2005 行列转换

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