试试
select itemno, type
,I2=(SELECT TOP 1 SUM(qty) FROM 表 A WHERE A.TYPE=B.TYPE AND A.TYPE='I2' )
,I3=(SELECT TOP 1 SUM(qty) FROM 表 A WHERE A.TYPE=B.TYPE AND A.TYPE='I3')
,W1=(SELECT TOP 1 SUM(qty) FROM 表 A WHERE A.TYPE=B.TYPE AND A.TYPE='W1')
FROM 表 b
select itemno, type
,I2=(SELECT TOP 1 SUM(qty) FROM 表 A WHERE A.TYPE=B.TYPE AND A.TYPE='I2' )
,I3=(SELECT TOP 1 SUM(qty) FROM 表 A WHERE A.TYPE=B.TYPE AND A.TYPE='I3')
,W1=(SELECT TOP 1 SUM(qty) FROM 表 A WHERE A.TYPE=B.TYPE AND A.TYPE='W1')
FROM 表 b
I2=sum(case type when 'I2' then qty else 0 end),
I3=sum(case type when 'I3' then qty else 0 end),
W1=sum(case type when 'W1' then qty else 0 end)
from table1
group by itemno,Type
sum(case when type='i2' then qty else 0 end) as I2,
sum(case when type='I3' then qty else 0 end) as I3,
sum(case when type='W1' then qty else 0 end) as W1FROM tb
group by itemno,type
order by itemno
sum(case when type='i2' then qty else 0 end) as I2,
sum(case when type='I3' then qty else 0 end) as I3,
sum(case when type='W1' then qty else 0 end) as W1FROM tb
group by itemno,type
order by itemno
Select @SQL='select itemno,type'
Select @SQL=@SQL+',sum(case '+[type]+' when '+[type]+' then qty else 0 end ) '+[type]+'
from (select distinct type from 表) as a
select @SQL=@SQL+' FROM 表 group by itemno,type order by itemno 'exec (@SQL)
-----------create table 表 (itemno varchar(30),type varchar(10),qty float)
insert into 表
select 'OT000206937292','I2',1146.89
union all select 'OT000206937292','I3',169
union all select 'OT0206937292','W1',-3259
union all select 'OT0104600C01','I2',-1777
union all select 'OT0104600C01','W1',-220
union all select 'OT0109609X02','I2',-54
union all select 'OT0109609X02','I3',300
union all select 'OT0109724S01','I2',6
union all select 'OT0109724S01','I3',350declare @SQL varchar(8000)
Select @SQL='select itemno,type'
Select @SQL=@SQL+',sum(case when type='''+[type]+''' then qty else 0 end ) '+[type]+''
from (select distinct type from 表) as a
select @SQL=@SQL+' FROM 表 group by itemno,type order by itemno 'exec (@SQL)drop table 表结果:itemno type I2 W1 I3
-------------------------------------------------------------OT000206937292 I2 1146.8900000000001 0.0 0.0
OT000206937292 I3 0.0 169.0 0.0
OT0104600C01 I2 -1777.0 0.0 0.0
OT0104600C01 W1 0.0 0.0 -220.0
OT0109609X02 I2 -54.0 0.0 0.0
OT0109609X02 I3 0.0 300.0 0.0
OT0109724S01 I2 6.0 0.0 0.0
OT0109724S01 I3 0.0 350.0 0.0
OT0206937292 W1 0.0 0.0 -3259.0
declare @SQL varchar(8000)
Select @SQL='select itemno,type'
Select @SQL=@SQL+',sum(case '+[type]+' when '+[type]+' then qty else 0 end ) '+[type]+'
from (select distinct type from 表) as a
select @SQL=@SQL+' FROM 表 group by itemno,type order by itemno 'exec (@SQL)