A: p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
--------------
select p_id, s1_id =case when s_id='01' then p_num else 0 end,
s2_id =case when s_id='02' then p_uum else 0 end,
s3_id =case when s_id='03' then p_num else 0 end
from t
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
--------------
select p_id, s1_id =case when s_id='01' then p_num else 0 end,
s2_id =case when s_id='02' then p_uum else 0 end,
s3_id =case when s_id='03' then p_num else 0 end
from t
insert @tb select 1,10,'01'
union all select 1,12,'02'
union all select 2,8,'02'
union all select 3,11,'01'
union all select 3,8,'03'
select * from @tb--select p_ID,case from @tb group by p_ID
select p_id, sum(case when s_id='01' then p_Num else 0 end) s1_id,
sum(case when s_id='02' then p_Num else 0 end) s2_id,
sum(case when s_id='03' then p_Num else 0 end) s3_id
from @tb group by p_id
drop table test1
create table test1(
p_id int,
p_Num int,
s_id varchar(10)
)
insert into test1
select 1,10,'01' union
select 1,12,'02' union
select 2,8,'01' union
select 3,11,'01' union
select 3,8,'03'
select * from test1
select p_id,s1_id=sum(case when s_id='01' then p_num else '0' end),
s2_id=sum(case when s_id='02' then p_num else '0' end),
s3_id=sum(case when s_id='03' then p_num else '0' end)
from test1 group by p_id
2 max(decode(tt.s_id,'01',tt.p_num,0)) as s_id_01,
3 max(decode(tt.s_id,'02',tt.p_num,0)) as s_id_02,
4 max(decode(tt.s_id,'03',tt.p_num,0)) as s_id_03
5 from (select 1 as p_ID,10 as p_Num,'01' as s_id from dual
6 union all
7 select 1 as p_ID,12 as p_Num,'02' as s_id from dual
8 union all
9 select 2 as p_ID,8 as p_Num,'01' as s_id from dual
10 union all
11 select 3 as p_ID,11 as p_Num,'01' as s_id from dual
12 union all
13 select 3 as p_ID,8 as p_Num,'03' as s_id from dual
14 )tt
15 group by tt.p_id; P_ID S_ID_01 S_ID_02 S_ID_03
---------- ---------- ---------- ----------
1 10 12 0
2 8 0 0
3 11 0 8
insert t select 1,10,'01'
union all select 1,12,'02'
union all select 2,8,'01'
union all select 3,11,'01'
union all select 3,8,'03'declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when s_id='''+s_id+''' then p_Num else 0 end) as [s'+rtrim(convert(int,s_id))+'_id]'
from t
group by s_idselect @sql='select p_ID'+@sql+' from t group by p_ID'
exec (@sql)/*
p_ID s1_id s2_id s3_id
----------- ----------- ----------- -----------
1 10 12 0
2 8 0 0
3 11 0 8
*/drop table t
select p_id,sum(case when s_id='01' then p_num else 0 end) as s1_id,
sum(case when s_id='02' then p_num else 0 end) as s2_id,
sum(case when s_id='03' then p_num else 0 end) as s3_id
from # group by p_id
p_id int,
p_Num int,
s_id varchar(10)
)select distinct p_id,s1_id = isnull((select sum(p_Num) from test1 where p_id = a.p_id and s_id = '01'),0)
,s2_id = isnull((select sum(p_Num) from test1 where p_id = a.p_id and s_id = '02'),0)
,s3_id = isnull((select sum(p_Num) from test1 where p_id = a.p_id and s_id = '03'),0)
from test1 a---------------------------------------
1 10 12 0
2 8 0 0
3 11 0 8
insert @tb select 1,10,'01'
union all select 1,12,'02'
union all select 2,8,'01'
union all select 3,11,'01'
union all select 3,8,'03'
select p_ID,s1_id=sum(case s_id when 01 then p_num else 0 end),
s2_id=sum(case s_id when 02 then p_num else 0 end),
s2_id=sum(case s_id when 03 then p_num else 0 end)
from @tb
group by p_ID
sum(case when s_id=2 then p_num else 0 end) as s2_id,
sum(case when s_id=3 then p_num else 0 end) as s3_id from test1
group by p_id结果:
p_id s1_id s2_id s3_id
----------- ----------- ----------- -----------
1 10 12 0
2 8 0 0
3 11 0 8
set @sql= ' '
select @sql=@sql+ ',sum(case when s_id= '+convert(varchar(4),s_id)+' then p_Num else 0 end) as [s'+rtrim(convert(varchar(10),s_id))+'_id] '
from a
group by s_id select @sql= 'select p_ID '+@sql+ ' from a group by p_ID '
exec (@sql)
和查学生成绩单一样select distinct x.p_ID,isnull((select p_Num from table_name where s_id='1'and p_ID=x.p_ID),'0') as s1_id,
isnull((select P_Num from table_name where s_id='2'and p_ID = x.P_ID),'0') as s2_id,
isnull((select p_Num from table_name where s_id='3'and P_ID= x.p_id ),'0') as s3_id
from table_name as x