如果"加工类型"固定只有以上几种类型,用如下SQL语句即可:
------------------------------------------------------------------------select
模号 = a.MoldId,
工件 = a.WorkpieceId,
铜公 = a.CopperId,
爆公数量 = a.BurstCopper,
粗公数量 = a.RoughCopper,
幼公数量 = a.ChildrenCopper,
CNC = max(case ProcessType when 'CNC' then State end),
车床 = max(case ProcessType when '车床' then State end),
铣床 = max(case ProcessType when '铣床' then State end),
线切割 = max(case ProcessType when '线切割' then State end),
雕刻 = max(case ProcessType when '雕刻' then State end),
执模 = max(case ProcessType when '执模' then State end),
EDM = max(case ProcessType when 'EDM' then State end)
from
Coppers a
inner join
Processes b
on
a.MoldId = b.MoldId
group by
a.MoldId,a.WorkpieceId,a.CopperId,a.BurstCopper,a.RoughCopper,a.ChildrenCopper
------------------------------------------------------------------------select
模号 = a.MoldId,
工件 = a.WorkpieceId,
铜公 = a.CopperId,
爆公数量 = a.BurstCopper,
粗公数量 = a.RoughCopper,
幼公数量 = a.ChildrenCopper,
CNC = max(case ProcessType when 'CNC' then State end),
车床 = max(case ProcessType when '车床' then State end),
铣床 = max(case ProcessType when '铣床' then State end),
线切割 = max(case ProcessType when '线切割' then State end),
雕刻 = max(case ProcessType when '雕刻' then State end),
执模 = max(case ProcessType when '执模' then State end),
EDM = max(case ProcessType when 'EDM' then State end)
from
Coppers a
inner join
Processes b
on
a.MoldId = b.MoldId
group by
a.MoldId,a.WorkpieceId,a.CopperId,a.BurstCopper,a.RoughCopper,a.ChildrenCopper
------------------------------------------------------------------
select
模号 = a.MoldId,
工件 = a.WorkpieceId,
铜公 = a.CopperId,
爆公数量 = a.BurstCopper,
粗公数量 = a.RoughCopper,
幼公数量 = a.ChildrenCopper,
CNC = max(case b.ProcessType when 'CNC' then State end),
车床 = max(case b.ProcessType when '车床' then State end),
铣床 = max(case b.ProcessType when '铣床' then State end),
线切割 = max(case b.ProcessType when '线切割' then State end),
雕刻 = max(case b.ProcessType when '雕刻' then State end),
执模 = max(case b.ProcessType when '执模' then State end),
EDM = max(case b.ProcessType when 'EDM' then State end)
from
Coppers a
inner join
Processes b
on
a.MoldId = b.MoldId
group by
a.MoldId,a.WorkpieceId,a.CopperId,
a.BurstCopper,a.RoughCopper,a.ChildrenCopper
2、动态SQL语句(适合"加工类型"类不固定的情况):
------------------------------------------------------------------
declare @s varchar(8000)
set @s = ''
select
@s = @s + ','+ProcessType
+ '=max(case b.ProcessType when '''+ ProcessType+''' then State end)'
from
Processes
group by
ProcessTypeset @s = 'select 模号 = a.MoldId,'
+' 工件 = a.WorkpieceId,'
+' 铜公 = a.CopperId,'
+' 爆公数量 = a.BurstCopper,'
+' 粗公数量 = a.RoughCopper,'
+' 幼公数量 = a.ChildrenCopper'
+ @s
+' from Coppers a inner join Processes b on a.MoldId = b.MoldId'
+' group by a.MoldId,a.WorkpieceId,a.CopperId'
+' ,a.BurstCopper,a.RoughCopper,a.ChildrenCopper'
exec(@s)
如果是固定的。则直接写sql语句就可以实现。
如果不固定,需要动态组织sql语句,把动的变成固定的。
楼上写的已经很详细。