create table 表(bmbh varchar(3),fylb varchar(3),fyje float) insert into 表 select '003','001',1400.00 union all select '004','001',100 union all select '003','002',12300 union all select '003','003',654 union all select '004','003',456 union all select '005','003',56 union all select '003','005',4564 godeclare @s varchar(8000) set @s='' select @s=@s+',['+fylb+']=sum(case fylb when '''+fylb+''' then fyje end)' from (select distinct fylb from 表) a exec('select bmbh'+@s+' from 表 group by bmbh') godrop table 表/*--测试结果 bmbh 001 002 003 005 ---- -------- -------- -------- ------- 003 1400.0 12300.0 654.0 4564.0 004 100.0 NULL 456.0 NULL 005 NULL NULL 56.0 NULL--*/
create table 表(bmbh varchar(3),fylb varchar(3),fyje float) insert into 表 select '003','001',1400.00 union all select '004','001',100 union all select '003','002',12300 union all select '003','003',654 union all select '004','003',456 union all select '005','003',56 union all select '003','005',4564 godeclare @s varchar(8000) set @s='' select @s=@s+',[fylb'+fylb+']=sum(case fylb when '''+fylb+''' then fyje end)' from (select distinct fylb from 表) a exec('select bmbh'+@s+' from 表 group by bmbh') godrop table 表/*--测试结果 bmbh fylb001 fylb002 fylb003 fylb005 ---- ---------- ---------- ---------- -------- 003 1400.0 12300.0 654.0 4564.0 004 100.0 NULL 456.0 NULL 005 NULL NULL 56.0 NULL--*/
from (select distinct fylb from 表) a为什么要上面这一句呢,能说说这句话的功能么,多谢了!
select @s=@s+',['+fylb+']=sum(case fylb when '''+fylb+''' then fyje end)' from (select distinct fylb from 表) a print @s -- 注意这里,你打印出来,帖到上面慢慢体会就知道了。 exec('select bmbh'+@s+' from 表 group by bmbh')
insert into 表
select '003','001',1400.00
union all select '004','001',100
union all select '003','002',12300
union all select '003','003',654
union all select '004','003',456
union all select '005','003',56
union all select '003','005',4564
godeclare @s varchar(8000)
set @s=''
select @s=@s+',['+fylb+']=sum(case fylb when '''+fylb+''' then fyje end)'
from (select distinct fylb from 表) a
exec('select bmbh'+@s+' from 表 group by bmbh')
godrop table 表/*--测试结果
bmbh 001 002 003 005
---- -------- -------- -------- -------
003 1400.0 12300.0 654.0 4564.0
004 100.0 NULL 456.0 NULL
005 NULL NULL 56.0 NULL--*/
insert into 表
select '003','001',1400.00
union all select '004','001',100
union all select '003','002',12300
union all select '003','003',654
union all select '004','003',456
union all select '005','003',56
union all select '003','005',4564
godeclare @s varchar(8000)
set @s=''
select @s=@s+',[fylb'+fylb+']=sum(case fylb when '''+fylb+''' then fyje end)'
from (select distinct fylb from 表) a
exec('select bmbh'+@s+' from 表 group by bmbh')
godrop table 表/*--测试结果
bmbh fylb001 fylb002 fylb003 fylb005
---- ---------- ---------- ---------- --------
003 1400.0 12300.0 654.0 4564.0
004 100.0 NULL 456.0 NULL
005 NULL NULL 56.0 NULL--*/
from (select distinct fylb from 表) a
print @s -- 注意这里,你打印出来,帖到上面慢慢体会就知道了。
exec('select bmbh'+@s+' from 表 group by bmbh')