表
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