mssql 行列转换（置换）

转换前　查寻的结果为　：

 

Area　　　　　　Date　　　　　Count
BeiJing　　　 　2007-01-01　 100000
GuangZhou　　2007-01-01 　200000
BeiJing 　　　　2007-02-19 　300000
GuangZhou　　2007-02-19　 400000
BeiJing　　　　2007-03-21 　　500000
GuangZhou 　2007-03-21　　 600000

 

转化后（求和）：

　　Area　　　2007-01-01　2007-02-19 　2007-03-21　

　　BeiJing 　　　　100000 　300000 　　　　500000
　GuangZhou 　200000 　　400000 　　　　600000

 

create table #TABLE (Area varchar(10), Date varchar(10), Count int)
go
insert into #TABLE (Area, Date, Count)
values ('BeiJing', '2007-01-01',100000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou', '2007-01-01',200000)
insert into #TABLE (Area, Date, Count)
values ('BeiJing','2007-02-19',300000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou','2007-02-19',400000)
insert into #TABLE (Area, Date, Count)
values ('BeiJing','2007-03-21',500000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou','2007-03-21',600000)
go

--mssql2000下验证 通过
select * from  #table
SELECT Area,
 '2007-01-01'=SUM(CASE Date WHEN '2007-01-01'  THEN [Count] END),
 '2007-02-19'=SUM(CASE Date WHEN '2007-02-19'  THEN [Count] END),
 '2007-03-21'=SUM(CASE Date WHEN '2007-03-21'  THEN [Count] END)
FROM #table
GROUP BY Area
go

 

------mssql2005下验证 通过--------------------------------------------------------------------------------------
SELECT *  FROM
#TABLE
PIVOT(SUM([Count]) FOR Date IN (
 [2007-01-01],[2007-02-19],[2007-03-21])) b

 

 

 

/*----------------demo----------------------------------------------------------------------------------------*/

/*  create table Inventory (
     item  varchar(100),
     color varchar(100),
     quantity int
  )*/
  /*
 
  insert into Inventory values('Table','Blue',124);
  insert into Inventory values('Table','Red',223);
  insert into Inventory values('Chair','Blue',101);
  insert into Inventory values('Chair','Red',null);
  insert into Inventory values('Chair','Red',210); */
 
 按Item分类，将数据按下列方式进行统计显示
         Item  Red  Blue

 

  select  item,'Blue'=sum(case color when 'Blue' then quantity end ),
              'Red'=sum(case color when 'Red' then quantity end)
              from inventory group by item