超级难的问题，请求大家帮助,谢谢
有如下一个表
emp_id t_id t_year t_month t_count
001 1 2004 01 100
001 2 2005 01 100
002 3 2004 01 200
003 4 2005 01 200
004 5 2004 01 100
004 6 2005 01 100
我想得到如下的结果
emp_id 2004 2005
001 100 100
002 200 null
003 null 200
004 100 100
请问用case语句的话怎么实现呢,谢谢了!
[解决办法]
select emp_id ,
sum(case when t_year =2004 then t_count end) as [2004],
sum(case when t_year =2005 then t_count end) as [2005]
from T
group by emp_id
[解决办法]
select emp_id,
sum(case when t_year= '2004 ' then t_count else 0 end) [2004],
sum(case when t_year= '2005 ' then t_count else 0 end) [2005]
from tab group by emp_id
[解决办法]
select emp_id,sum(case when t_year= '2004 ' then t_count else 0 end),
sum(case when t_year= '2005 ' then t_count else 0 end)
from ss group by emp_id order by emp_id
[解决办法]
这个交叉表就可以了
create table #(A varchar(20),B varchar(20),C varchar(20))
insert into #
select 'x ', '01 ', 'aaa ' union
select 'x ', '02 ', 'bbb ' union
select 'x ', '03 ', 'ccc ' union
select 'y ', '01 ', 'ddd ' union
select 'y ', '03 ', 'eee ' union
select 'z ', '04 ', 'fff '
go
--处理语句
declare
@sql varchar(8000),
@sq varchar(8000)
select @sql= ' '
select @sql=@sql+ ',[ '+B+ ']=max(case B when ' ' '+B+ ' ' ' then C end) '
from #
group by B
set @sq= 'select A '+@sql+ ' from # group by A '
print @sq
exec(@sq)
--删除测试表
drop table #
go
[解决办法]
--如表名为T
declare @sql varchar(8000)
select @sql= 'select emp_id '
select @sql=@sql+ ', ' ' '+cast(t_year as varchar(4))+ ' ' '=sum(case when t_year= '+cast(t_year as varchar(4))+ ' then t_count end) '
from (select distinct t_year from t) a
select @sql=@sql+ ' from t group by emp_id '
exec(@sql)
