40分请叫一个sql 语句?在线等待!!!!!!

[wgzy3250]

我有一表如下
itemno type qty
OT000206937292 I2 1146.89
OT000206937292 I3 169
OT0206937292 W1 -3259
OT0104600C01 I2 -1777
OT0104600C01 W1 -220
OT0109609X02 I2 -54
OT0109609X02 I3 300
OT0109724S01 I2 6
OT0109724S01 I3 350
现在要做成一报表表头如下
itemno Type I2 I3 W1
后三列就是按Type去sum数量,请问sql如何下?

[zhangzs8896]

试试
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

[Andy__Huang]

select itemno,Type,
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

[yesterday2000]

select 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 W1

FROM tb
group by itemno,type
order by itemno

[hglhyy]

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)

[penghongxia]

select 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 W1

FROM tb
group by itemno,type
order by itemno

[azsoft]

try:

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)

[azsoft]

动态列:
-----------

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',350

declare @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