sql 2005中的行转列和列转行

1、列转行

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

--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

 

2行转列

CREATE TABLE salesByMonth
(
year char(4),
month char(3),
amount money,
PRIMARY KEY (year, month)
)

INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Jan', 789.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Feb', 389.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Mar', 8867.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Apr', 778.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','May', 78.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Jun', 9.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Jul', 987.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Aug', 866.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Sep', 7787.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Oct', 85576.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Nov', 855.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Dec', 5878.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2005','Jan', 7.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2005','Feb', 6868.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2005','Mar', 688.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2005','Apr', 9897.0000)
--查看数据
select * from salesByMonth
--按照常规行转列
SELECT *
FROM salesByMonth 
PIVOT ( SUM(amount) FOR month IN
  ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
  )t
显示结果
year Jan                   Feb                   Mar                   Apr                   May                   Jun                   Jul                   Aug                   Sep                   Oct                   Nov                   Dec
---- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
2004 789.00                389.00                8867.00               778.00                78.00                 9.00                  987.00                866.00                7787.00               85576.00              855.00                5878.00
2005 7.00                  6868.00               688.00                9897.00               NULL                  NULL                  NULL                  NULL                  NULL                  NULL                  NULL                  NULL

(2 行受影响)


而我的需求是相反的操作，我要求把year作为列显示，呈现类似如下的结果：
    2004 2005
Jan X     X 
Feb 
Mar
Apr
May

 

2、行转列

CREATE TABLE salesByMonth

(

year char(4),

month char(3),

amount money,

PRIMARY KEY (year, month)

)

 

INSERT INTO salesByMonth (year, month, amount)

VALUES('2004','Jan', 789.0000)

INSERT INTO salesByMonth (year, month, amount)

VALUES('2004','Feb', 389.0000)

INSERT INTO salesByMonth (year, month, amount)

VALUES('2004','Mar', 8867.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2004','Apr', 778.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2004','May', 78.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2004','Jun', 9.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2004','Jul', 987.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2004','Aug', 866.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2004','Sep', 7787.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2004','Oct', 85576.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2004','Nov', 855.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2004','Dec', 5878.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2005','Jan', 7.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2005','Feb', 6868.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2005','Mar', 688.0000)

INSERT INTO  salesByMonth (year, month, amount)

VALUES('2005','Apr', 9897.0000)

SELECT *

-- [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]

FROM ( SELECT amount, month

FROM salesByMonth ) AS salesByMonth

PIVOT ( SUM(amount) FOR month IN

([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])

) AS ourPivot

 

3、----------------------

SQL SERVER 2005中新增加了两个关系运算符 PIVOT/ UNPIVOT，能够实现表中的列转换到行，以及行到列的转换工作。

举例，还是先创建测试数据表

CREATE   TABLE  sales.salesByMonth
(
year   char ( 4 ),
month   char ( 3 ),
amount  money ,
PRIMARY   KEY  ( year ,  month )
)

INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Jan ' ,  789.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Feb ' ,  389.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Mar ' ,  8867.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Apr ' ,  778.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' May ' ,  78.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Jun ' ,  9.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Jul ' ,  987.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Aug ' ,  866.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Sep ' ,  7787.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Oct ' ,  85576.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Nov ' ,  855.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2004 ' , ' Dec ' ,  5878.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2005 ' , ' Jan ' ,  7.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2005 ' , ' Feb ' ,  6868.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2005 ' , ' Mar ' ,  688.0000 )
INSERT   INTO  sales.salesByMonth ( year ,  month , amount)
VALUES ( ' 2005 ' , ' Apr ' ,  9897.0000 )

我们想要得到类似这样的结果：
Year  Jan            Feb             Mar       ..............
----- ---------- ----------- -----------
2004 789.0000 389.0000     8867.0000 .............
2005 7.0000     6868.0000   688.0000 ..............

用 上一篇文章介绍的方法当然可以实现，但现在这里想要的列是固定的，不是动态的，就是12个月，所以也可以这样子来用：

SELECT   year ,
SUM ( case   when   month   =   ' Jan '   then  amount  else   0   end )  AS   ' Jan ' ,
SUM ( case   when   month   =   ' Feb '   then  amount  else   0   end )  AS   ' Feb ' ,
SUM ( case   when   month   =   ' Mar '   then  amount  else   0   end )  AS   ' Mar ' ,
SUM ( case   when   month   =   ' Apr '   then  amount  else   0   end )  AS   ' Apr ' ,
SUM ( case   when   month   =   ' May '   then  amount  else   0   end )  AS   ' May ' ,
SUM ( case   when   month   =   ' Jun '   then  amount  else   0   end )  AS   ' Jun ' ,
SUM ( case   when   month   =   ' Jul '   then  amount  else   0   end )  AS   ' Jul ' ,
SUM ( case   when   month   =   ' Aug '   then  amount  else   0   end )  AS   ' Aug ' ,
SUM ( case   when   month   =   ' Sep '   then  amount  else   0   end )  AS   ' Sep ' ,
SUM ( case   when   month   =   ' Oct '   then  amount  else   0   end )  AS   ' Oct ' ,
SUM ( case   when   month   =   ' Nov '   then  amount  else   0   end )  AS   ' Nov ' ,
SUM ( case   when   month   =   ' Dec '   then  amount  else   0   end )  AS   ' Dec '
FROM  sales.salesByMonth
GROUP   by   year

但这样事实上还是相当麻烦的，现在SQLSERVER2005中有更方便的实现方法。

SELECT   Year , [ Jan ] , [ Feb ] , [ Mar ] , [ Apr ] , [ May ] , [ Jun ] , [ Jul ] , [ Aug ] , [ Sep ] , [ Oct ] , [ Nov ] , [ Dec ]
FROM  (
   SELECT   year , amount,  month
   FROM  sales.salesByMonth )  AS  salesByMonth
PIVOT (  SUM (amount)  FOR   month   IN
  ( [ Jan ] , [ Feb ] , [ Mar ] , [ Apr ] , [ May ] , [ Jun ] , [ Jul ] , [ Aug ] , [ Sep ] , [ Oct ] , [ Nov ] , [ Dec ] )
  )  AS  ourPivot
ORDER   BY   Year

就是这样，很简单的用法，效果是完全一样的。

我们再尝试一下把year去掉：

SELECT   [ Jan ] , [ Feb ] , [ Mar ] , [ Apr ] , [ May ] , [ Jun ] , [ Jul ] , [ Aug ] , [ Sep ] , [ Oct ] , [ Nov ] , [ Dec ]
FROM  (  SELECT  amount,  month
FROM  sales.salesByMonth )  AS  salesByMonth
PIVOT (  SUM (amount)  FOR   month   IN
( [ Jan ] , [ Feb ] , [ Mar ] , [ Apr ] , [ May ] , [ Jun ] , [ Jul ] , [ Aug ] , [ Sep ] , [ Oct ] , [ Nov ] , [ Dec ] )
)  AS  ourPivot

得到的结果是：
Jan                 Feb                 Mar             ...
---------- ------------ -----------
796.0000     7257.0000     9555.0000     ...

同一个月份的数据累加到一起。


再给个微软官方的例子：