转换前 查寻的结果为 :
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