原始数据如下
希望用sql 查询产生如下
------解决思路----------------------
with t(产品编号,工序编号,开始时间,调整时间,备注) as (
select 'P001','G01','2015-09-18 08:00:00','2015-09-19 08:00:00','aa' union all
select 'P001','G02','2015-09-18 09:00:00','2015-09-19 09:00:00','bb' union all
select 'P001','G03','2015-09-18 10:00:00','2015-09-19 10:00:00','cc' union all
select 'P002','G02','2015-09-18 11:00:00','2015-09-19 11:00:00','dd' union all
select 'P002','G03','2015-09-18 12:00:00','2015-09-19 12:00:00','' union all
select 'P002','G04','2015-09-18 13:00:00','2015-09-19 13:00:00','' union all
select 'P002','G05','2015-09-18 14:00:00','2015-09-19 14:00:00',''
)
,tt as(
select
t.产品编号
,'工序' 工序编号
,'G01' 工序1
,'G02' 工序2
,'G03' 工序3
,'G04' 工序4
,'G05' 工序5
,1 sort
from t
group by t.产品编号
union all
select
t.产品编号
,'开始时间' 工序编号
,max(case 工序编号 when 'G01' then t.开始时间 else '' end) 工序1
,max(case 工序编号 when 'G02' then t.开始时间 else '' end) 工序2
,max(case 工序编号 when 'G03' then t.开始时间 else '' end) 工序3
,max(case 工序编号 when 'G04' then t.开始时间 else '' end) 工序4
,max(case 工序编号 when 'G05' then t.开始时间 else '' end) 工序5
,2 sort
from t
group by t.产品编号
union all
select
t.产品编号
,'调整时间' 工序编号
,max(case 工序编号 when 'G01' then t.调整时间 else '' end) 工序1
,max(case 工序编号 when 'G02' then t.调整时间 else '' end) 工序2
,max(case 工序编号 when 'G03' then t.调整时间 else '' end) 工序3
,max(case 工序编号 when 'G04' then t.调整时间 else '' end) 工序4
,max(case 工序编号 when 'G05' then t.调整时间 else '' end) 工序5
,3 sort
from t
group by t.产品编号
union all
select
t.产品编号
,'备注' 工序编号
,max(case 工序编号 when 'G01' then t.备注 else '' end) 工序1
,max(case 工序编号 when 'G02' then t.备注 else '' end) 工序2
,max(case 工序编号 when 'G03' then t.备注 else '' end) 工序3
,max(case 工序编号 when 'G04' then t.备注 else '' end) 工序4
,max(case 工序编号 when 'G05' then t.备注 else '' end) 工序5
,4 sort
from t
group by t.产品编号
)
select
case sort when 1 then tt.产品编号 else '' end 产品编号
,tt.工序编号,tt.工序1,tt.工序2,tt.工序3,tt.工序4,tt.工序5
from tt
order by tt.产品编号,tt.sort
------解决思路----------------------
WITH cet AS (
select MoldNO ,
[工序1]=max(CASE ProceNO WHEN 'G01' THEN SDate END),
[工序2]=max(CASE ProceNO WHEN 'G02' THEN SDate END),
[工序3]=max(CASE ProceNO WHEN 'G03' THEN SDate END),
[工序4]=max(CASE ProceNO WHEN 'G04' THEN SDate END),
[工序5]=max(CASE ProceNO WHEN 'G05' THEN SDate END)
FROM TB_Process
GROUP BY MoldNO
UNION ALL
select MoldNO ,
[工序1]=max(CASE ProceNO WHEN 'G01' THEN EDate END),
[工序2]=max(CASE ProceNO WHEN 'G02' THEN EDate END),
[工序3]=max(CASE ProceNO WHEN 'G03' THEN EDate END),
[工序4]=max(CASE ProceNO WHEN 'G04' THEN EDate END),
[工序5]=max(CASE ProceNO WHEN 'G05' THEN EDate END)
FROM TB_Process
GROUP BY MoldNO
UNION ALL
select MoldNO ,
[工序1]=max(CASE ProceNO WHEN 'G01' THEN Remark END),
[工序2]=max(CASE ProceNO WHEN 'G02' THEN Remark END),
[工序3]=max(CASE ProceNO WHEN 'G03' THEN Remark END),
[工序4]=max(CASE ProceNO WHEN 'G04' THEN Remark END),
[工序5]=max(CASE ProceNO WHEN 'G05' THEN Remark END)
FROM TB_Process
GROUP BY MoldNO
)
SELECT * FROM cet ORDER BY moldno
------解决思路----------------------
;with cte1 as
(
select '1' as mid,
[G01] as G1,[G02] as G2,[G03] as G3,[G04] as G4,[G05] as G5
from (select gid from tbl18 group by gid) AS tbl
PIVOT
(
min(gid) for [gid] in
([G01],[G02],[G03],[G04],[G05])
) as T
),
cte2 as
(
select '1' as mid, id from tbl18 group by id
)
select T.id as [产品编号], T.pc as [排程],
isnull(G1, '') as [工序1],
isnull(G2, '') as [工序2],
isnull(G3, '') as [工序3],
isnull(G4, '') as [工序4],
isnull(G5, '') as [工序5]
from
(
select
'0' as sort, cte2.id, '工序' as pc,
cte1.G1, cte1.G2, cte1.G3,
cte1.G4, cte1.G5, cte2.id as tid
from cte2
left join
cte1
on cte2.mid =cte1.mid
union all
select
'1' as sort, '' as id, '开始时间' as [pc],
convert(nvarchar(20),(G1.sdt),120) as [G1],
convert(nvarchar(20),(G2.sdt),120) as [G2],
convert(nvarchar(20),(G3.sdt),120) as [G3],
convert(nvarchar(20),(G4.sdt),120) as [G4],
convert(nvarchar(20),(G5.sdt),120) as [G5],
cte2.id as tid
from cte2
left join
(select id, sdt from tbl18 where gid = 'G01') as G1
on cte2.id = G1.id
left join
(select id, sdt from tbl18 where gid = 'G02') as G2
on cte2.id = G2.id
left join
(select id, sdt from tbl18 where gid = 'G03') as G3
on cte2.id = G3.id
left join
(select id, sdt from tbl18 where gid = 'G04') as G4
on cte2.id = G4.id
left join
(select id, sdt from tbl18 where gid = 'G05') as G5
on cte2.id = G5.id
union all
select
'1' as sort, '' as id, '调整时间' as [pc],
convert(nvarchar(20),(G1.edt),120) as [G1],
convert(nvarchar(20),(G2.edt),120) as [G2],
convert(nvarchar(20),(G3.edt),120) as [G3],
convert(nvarchar(20),(G4.edt),120) as [G4],
convert(nvarchar(20),(G5.edt),120) as [G5],
cte2.id as tid
from cte2
left join
(select id, edt from tbl18 where gid = 'G01') as G1
on cte2.id = G1.id
left join
(select id, edt from tbl18 where gid = 'G02') as G2
on cte2.id = G2.id
left join
(select id, edt from tbl18 where gid = 'G03') as G3
on cte2.id = G3.id
left join
(select id, edt from tbl18 where gid = 'G04') as G4
on cte2.id = G4.id
left join
(select id, edt from tbl18 where gid = 'G05') as G5
on cte2.id = G5.id
union all
select
'1' as sort, '' as id, '备注' as [pc],
convert(nvarchar(20),(G1.rmk),120) as [G1],
convert(nvarchar(20),(G2.rmk),120) as [G2],
convert(nvarchar(20),(G3.rmk),120) as [G3],
convert(nvarchar(20),(G4.rmk),120) as [G4],
convert(nvarchar(20),(G5.rmk),120) as [G5],
cte2.id as tid
from cte2
left join
(select id, rmk from tbl18 where gid = 'G01') as G1
on cte2.id = G1.id
left join
(select id, rmk from tbl18 where gid = 'G02') as G2
on cte2.id = G2.id
left join
(select id, rmk from tbl18 where gid = 'G03') as G3
on cte2.id = G3.id
left join
(select id, rmk from tbl18 where gid = 'G04') as G4
on cte2.id = G4.id
left join
(select id, rmk from tbl18 where gid = 'G05') as G5
on cte2.id = G5.id
) as T
order by T.tid, T.sort
額，貌似我这么写的最傻。。。