例子:ID CodeID Feedback SetpID
1 188 test 1
2 188 test1 1
3 188 test2 1
4 188 test3 1
5 188 test4 2
6 188 test5 2
7 188 test6 2
8 188 test7 3
9 188 test8 3
10 200 test11 1
11 200 test12 1
12 200 test34 2
13 200 test35 2
14 200 test36 2
现在小弟要实现如下结果:(统计每个code的setp的总数
code setp1 setp2 setp3 setp4 setp5 setp6 setp7 setp8 setp9
188 4 3 2
200 2 3
望高手指导下小弟 ,此sql如何写,弄了一个上午了, 头都大了。在此先谢谢各位了!!!!!
1 188 test 1
2 188 test1 1
3 188 test2 1
4 188 test3 1
5 188 test4 2
6 188 test5 2
7 188 test6 2
8 188 test7 3
9 188 test8 3
10 200 test11 1
11 200 test12 1
12 200 test34 2
13 200 test35 2
14 200 test36 2
现在小弟要实现如下结果:(统计每个code的setp的总数
code setp1 setp2 setp3 setp4 setp5 setp6 setp7 setp8 setp9
188 4 3 2
200 2 3
望高手指导下小弟 ,此sql如何写,弄了一个上午了, 头都大了。在此先谢谢各位了!!!!!
sum(case when SetpID = 2 then 1 else 0 end) Setp2,
......
from Table
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([ID] int,[CodeID] int,[Feedback] varchar(6),[SetpID] int)
insert [test]
select 1,188,'test',1 union all
select 2,188,'test1',1 union all
select 3,188,'test2',1 union all
select 4,188,'test3',1 union all
select 5,188,'test4',2 union all
select 6,188,'test5',2 union all
select 7,188,'test6',2 union all
select 8,188,'test7',3 union all
select 9,188,'test8',3 union all
select 10,200,'test11',1 union all
select 11,200,'test12',1 union all
select 12,200,'test34',2 union all
select 13,200,'test35',2 union all
select 14,200,'test36',2
declare @str varchar(max)
set @str=''
select @str=@str+','+'step'+LTRIM([SetpID])+'=sum(case when [SetpID]='+LTRIM([SetpID])
+' then 1 else 0 end)' from test group by [SetpID]
exec('select [CodeID]'+@str+' from test group by [CodeID]')/*
CodeID step1 step2 step3
188 4 3 2
200 2 3 0
*/