列变行，急！

[insert2003]

有表tb_Test
c_Filiale c_Month i_Money
广东 200501 100
广东 200502 200
广东 200503 300
上海 200501 120
上海 200502 220
上海 200503 320

要得到如下结果

200501 200502 200503
广东 100 200 300
上海 120 220 320

应该怎么写代码？

[samfeng_2003]

select c_Filiale,[200501]=isnull(sum(case when c_Month='200501' then i_Money end),0),
[200502]=isnull(sum(case when c_Month='200502' then i_Money end),0),
[200503]=isnull(sum(case when c_Month='200503' then i_Money end),0)
from tb_Test group by c_Filiale order by c_Filiale

[zlp321002]

select 地区=c_Filiale,
[200501]=sum(case when c_Filiale=A.c_Filiale and c_Month='200501' then i_Money end),
[200502]=sum(case when c_Filiale=A.c_Filiale and c_Month='200502' then i_Money end),
[200503]=sum(case when c_Filiale=A.c_Filiale and c_Month='200503' then i_Money end)
from T_Test A
group by c_Filiale

[zlp321002]

--测试环境
Create table T_Test(c_Filiale varchar(10),c_Month varchar(10),i_Money int)
insert into T_Test select '广东','200501',100
union all select '广东','200502',200
union all select '广东','200503',300
union all select '上海','200501',120
union all select '上海','200502',220
union all select '上海','200503',320
--查询

select 地区=c_Filiale,
[200501]=sum(case when c_Filiale=A.c_Filiale and c_Month='200501' then i_Money end),
[200502]=sum(case when c_Filiale=A.c_Filiale and c_Month='200502' then i_Money end),
[200503]=sum(case when c_Filiale=A.c_Filiale and c_Month='200503' then i_Money end)
from T_Test A
group by c_Filiale


--动态SQL
declare @s varchar(2000)
set @s='select 地区=c_Filiale'
select @s=@s+',['+c_Month+']=sum(case when c_Month='''+c_Month+''' then i_Money end )'
from T_Test A
group by c_Month
set @s=@s+' From T_Test Group by c_Filiale'
exec(@s)



--测试结果
地区 200501 200502 200503
---------- ----------- ----------- -----------
广东 100 200 300
上海 120 220 320

警告: 聚合或其它 SET 操作消除了空值。



地区 200501 200502 200503
---------- ----------- ----------- -----------
广东 100 200 300
上海 120 220 320

警告: 聚合或其它 SET 操作消除了空值。

[Rubi]

太多的了,可以参考sql2000的帮助交叉表

[insert2003]

呵
谢啦

[khyhli]

declare @s varchar(8000)
set @s=''
select @s=@s+',['+c_month+']=sum(case c_month when '''+c_month+''' then c_money end)' from tb_Test group by c_month
exec ('select c_filiable '+@s+' from tb_Test group by c_filiable')