有数据表如下:
CheckMonth Count_SH Count_BJ Count_GZ
2010-04 1570 1350 750
2010-05 1840 1400 850
2010-06 1950 1580 890
如何呈现为:
CityCount 2010-04 2010-05 2010-06
Count_SH 1570 1840 1950
Count_BJ 1350 1400 1580
Count_GZ 750 850 890
create table tb(CheckMonth varchar(10) , Count_SH int, Count_BJ int, Count_GZ int)
insert into tb values('2010-04' , 1570 , 1350, 750)
insert into tb values('2010-05' , 1840 , 1400, 850)
insert into tb values('2010-06' , 1950 , 1580, 890)
go
select b.name as CityCount,
max(case CheckMonth when '2010-04' then case b.name when 'Count_SH' then Count_SH when 'Count_BJ' then Count_BJ else Count_GZ end else 0 end) '2010-04',
max(case CheckMonth when '2010-05' then case b.name when 'Count_SH' then Count_SH when 'Count_BJ' then Count_BJ else Count_GZ end else 0 end) '2010-05',
max(case CheckMonth when '2010-06' then case b.name when 'Count_SH' then Count_SH when 'Count_BJ' then Count_BJ else Count_GZ end else 0 end) '2010-06'
from tb,(select distinct name from syscolumns where id = object_id('tb','U') and name <> 'CheckMonth') b
group by b.name
CityCount 2010-04 2010-05 2010-06
--------------- ------- ------- -------
Count_BJ 1350 1400 1580
Count_GZ 750 850 890
Count_SH 1570 1840 1950
http://topic.csdn.net/u/20101112/14/8c7a124b-3ccf-44d6-a091-dc81fac58daa.html?34109