行转列的老问题！求救
表
A,B,C,D,E
a,1,r,f,b
a,1,r,f,b
a,1,r,f,g
a,1,r,f,h
a,1,r,f,j
c,2,g,h,b
c,1,g,h,f
c,1,g,h,k
c,1,g,h,r
变成
A,C,D,b,g,h,j,f,k,r
a,r,f,2,1,1,1,0,0,0
c,g,h,2,0,0,0,1,1,1
这样的行转列怎么解决，拜托各位大大了！
[解决办法]
declare @sql varchar(8000)
set @sql = 'select A,C,D '
select @sql = @sql + ' , sum(case e when ' ' ' + e + ' ' ' then 1 else 0 end) [ ' + e + '] '
from (select distinct e from tb) as a
set @sql = @sql + ' from tb group by A,C,D '
exec(@sql)
[解决办法]
create table tb(A char(1),B char(1),C char(1),D char(1),E char(1))
insert into tb values( 'a ', '1 ', 'r ', 'f ', 'b ')
insert into tb values( 'a ', '1 ', 'r ', 'f ', 'b ')
insert into tb values( 'a ', '1 ', 'r ', 'f ', 'g ')
insert into tb values( 'a ', '1 ', 'r ', 'f ', 'h ')
insert into tb values( 'a ', '1 ', 'r ', 'f ', 'j ')
insert into tb values( 'c ', '2 ', 'g ', 'h ', 'b ')
insert into tb values( 'c ', '1 ', 'g ', 'h ', 'f ')
insert into tb values( 'c ', '1 ', 'g ', 'h ', 'k ')
insert into tb values( 'c ', '1 ', 'g ', 'h ', 'r ')
declare @sql varchar(8000)
set @sql = 'select A,C,D '
select @sql = @sql + ' , sum(case e when ' ' ' + e + ' ' ' then 1 else 0 end) [ ' + e + '] '
from (select distinct e from tb) as a
set @sql = @sql + ' from tb group by A,C,D '
exec(@sql)
/*
A C D b f g h j k r
---- ---- ---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
a r f 2 0 1 1 1 0 0
c g h 1 1 0 0 0 1 1
*/
drop table tb
