表TB1:首先要按产品和工序汇总数量,然后将工序字段列转行。这该如何是好?
看了一些列转行贴子,因才疏学浅,理解不深。望大师指点! (sql2000 + vb6)
CP GX SL
------ -------- ------------------
CP01 GX01 10
CP01 GX01 20
CP01 GX02 10
CP01 GX02 10
CP01 GX03 15
CP01 GX03 15
CP02 GX04 10
CP02 GX04 20
CP02 GX02 10
CP02 GX02 10
CP02 GX06 15
CP02 GX06 15结果显示如下:
CP GX01 GX02 GX03 GX04 GX06
---- ----- ------ ------ ----- ------
CP01 30 20 30
CP02 20 30 30
看了一些列转行贴子,因才疏学浅,理解不深。望大师指点! (sql2000 + vb6)
CP GX SL
------ -------- ------------------
CP01 GX01 10
CP01 GX01 20
CP01 GX02 10
CP01 GX02 10
CP01 GX03 15
CP01 GX03 15
CP02 GX04 10
CP02 GX04 20
CP02 GX02 10
CP02 GX02 10
CP02 GX06 15
CP02 GX06 15结果显示如下:
CP GX01 GX02 GX03 GX04 GX06
---- ----- ------ ------ ----- ------
CP01 30 20 30
CP02 20 30 30
select SUM(Case When GX='GX01' then SL end) as GX01,
SUM(Case When GX='GX02' then SL end) as GX02,
SUM(Case When GX='GX03' then SL end) as GX03,
SUM(Case When GX='GX04' then SL end) as GX04,
SUM(Case When GX='GX05' then SL end) as GX05,
SUM(Case When GX='GX06' then SL end) as GX06,
CP
from Tab1 Group CP Order by CP
SELECT CP,
SUM(CASE WHEN GX = 'GX01' THEN SL END) AS GX01,
SUM(CASE WHEN GX = 'GX02' THEN SL END) AS GX02,
SUM(CASE WHEN GX = 'GX03' THEN SL END) AS GX03,
SUM(CASE WHEN GX = 'GX04' THEN SL END) AS GX04,
SUM(CASE WHEN GX = 'GX05' THEN SL END) AS GX05,
SUM(CASE WHEN GX = 'GX06' THEN SL END) AS GX06
FROM TB1
GROUP BY CP
以这为准
insert @table
select 'CP01','GX01', 10 union
select 'CP01','GX01', 20 union
select 'CP01','GX02', 10 union
select 'CP01','GX02', 10 union
select 'CP01','GX03', 15 union
select 'CP01','GX03', 15 union select 'CP02','GX04', 10 union
select 'CP02','GX04', 20 union
select 'CP02','GX02', 10 union
select 'CP02','GX02', 10 union
select 'CP02','GX06', 15 union
select 'CP02','GX06', 15
select * from @TableSELECT CP,
SUM(CASE WHEN GX = 'GX01' THEN SL ELSE 0 END) AS GX01,
SUM(CASE WHEN GX = 'GX02' THEN SL ELSE 0 END) AS GX02,
SUM(CASE WHEN GX = 'GX03' THEN SL ELSE 0 END) AS GX03,
SUM(CASE WHEN GX = 'GX04' THEN SL ELSE 0 END) AS GX04,
SUM(CASE WHEN GX = 'GX05' THEN SL ELSE 0 END) AS GX05,
SUM(CASE WHEN GX = 'GX06' THEN SL ELSE 0 END) AS GX06
FROM @Table
GROUP BY CP
insert @table
select 'CP01','GX01', 10 union
select 'CP01','GX01', 20 union
select 'CP01','GX02', 10 union
select 'CP01','GX02', 10 union
select 'CP01','GX03', 15 union
select 'CP01','GX03', 15 union select 'CP02','GX04', 10 union
select 'CP02','GX04', 20 union
select 'CP02','GX02', 10 union
select 'CP02','GX02', 10 union
select 'CP02','GX06', 15 union
select 'CP02','GX06', 15
select * from @Tableselect cp,
sum(case GX when 'GX01' then SL ELSE 0 end ) as GX01,
sum(case GX when 'GX02' then SL ELSE 0 end ) as GX02,
sum(case GX when 'GX03' then SL ELSE 0 end ) as GX03,
sum(case GX when 'GX04' then SL ELSE 0 end ) as GX04,
sum(case GX when 'GX06' then SL ELSE 0 end ) as GX06
from @Table
group By Cp
insert t
select 'CP01','GX01', 10 union
select 'CP01','GX01', 20 union
select 'CP01','GX02', 10 union
select 'CP01','GX02', 10 union
select 'CP01','GX03', 15 union
select 'CP01','GX03', 15 union select 'CP02','GX04', 10 union
select 'CP02','GX04', 20 union
select 'CP02','GX02', 10 union
select 'CP02','GX02', 10 union
select 'CP02','GX06', 15 union
select 'CP02','GX06', 15
select * from TDECLARE @S AS VARCHAR(8000)
set @s='select cp'
select @S=@s+',SUM(CASE WHEN GX = '''+gx+''' THEN SL ELSE 0 END) AS '+ gx from (select gx from t group by gx)tt
set @s=@s+' from t group by cp'
EXECUTE(@S)输出结果:
CP01 30 10 15 0 0
CP02 0 10 0 30 15