新手select 问题
一张表的结构为
empno value
001 A
001 B
001 C
001 D
002 B
002 D
002 E
我需要把它转化成另外一种形式
empno A B C D E
001 1 1 1 1 0
002 0 1 0 1 1
请问这个sql语句该怎么写？
[解决办法]
declare @sql varchar(8000)
set @sql = 'select empno '
select @sql = @sql + ',[ ' + value + ']=sum(case value when ' ' ' + value + ' ' ' then 1 else 0 end) '
from table group by value
EXEC(@sql + ' from table group by empno ')
[解决办法]
select empno,A=sum(case value when 'A ' then 1 else 0 end),
B=sum(case value when 'B ' then 1 else 0 end),
C=sum(case value when 'C ' then 1 else 0 end),
D=sum(case value when 'D ' then 1 else 0 end),
E=sum(case value when 'E ' then 1 else 0 end)
from tablename group by empno
