表
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
这样的行转列怎么解决,拜托各位大大了!
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
这样的行转列怎么解决,拜托各位大大了!
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)
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