表mzk(id,mc,item,vl)
id mc item vl
2001 车间1 0 2500
2002 车间2 0 3000
2003 车间3 0 2400
5001 井A 1 88
5002 井B 1 90
2001 车间1 0 2100
2002 车间2 0 2400
2003 车间3 0 3100
5001 井A 1 100
5002 井B 1 105
......
-----------------------------
通过item的值(0,1)转为四列
id1 mc1 vl1 id2 mc2 vl2
2001 车间1 4600 5001 井A 188
2002 车间2 5400 5002 井B 195
2003 车间3 5500sql server
id mc item vl
2001 车间1 0 2500
2002 车间2 0 3000
2003 车间3 0 2400
5001 井A 1 88
5002 井B 1 90
2001 车间1 0 2100
2002 车间2 0 2400
2003 车间3 0 3100
5001 井A 1 100
5002 井B 1 105
......
-----------------------------
通过item的值(0,1)转为四列
id1 mc1 vl1 id2 mc2 vl2
2001 车间1 4600 5001 井A 188
2002 车间2 5400 5002 井B 195
2003 车间3 5500sql server
from (select *,rn=ROW_NUMBER() over(order by id1) from tb where item=1) a
left join (select *,rn=ROW_NUMBER() over(order by id1) from tb where item=0)b
on a.rn=b.rn
(
select 2001,'车间1',0,2500
union all select 2002,'车间2',0,3000
union all select 2003,'车间3',0,2400
union all select 5001,'井A',1,88
union all select 5002,'井B',1,90
union all select 2001,'车间1',0,2100
union all select 2002,'车间2',0,2400
union all select 2003,'车间3',0,3100
union all select 5001,'井A',1,100
union all select 5002,'井B',1,105
)
select a.id,a.mc,a.vl,b.id as id2,b.mc as mc2,b.vl as v12
from (select *,rn=ROW_NUMBER() over(order by id) from cte where item=0) a
left join (select *,rn=ROW_NUMBER() over(order by id) from cte where item=1)b
on a.rn=b.rn /*
id mc vl id2 mc2 v12
-----------------------------------
2001 车间1 2500 5001 井A 88
2001 车间1 2100 5001 井A 100
2002 车间2 2400 5002 井B 105
2002 车间2 3000 5002 井B 90
2003 车间3 2400 NULL NULL NULL
2003 车间3 3100 NULL NULL NULL
*/
(
select 2001,'车间1',0,2500
union all select 2002,'车间2',0,3000
union all select 2003,'车间3',0,2400
union all select 5001,'井A',1,88
union all select 5002,'井B',1,90
union all select 2001,'车间1',0,2100
union all select 2002,'车间2',0,2400
union all select 2003,'车间3',0,3100
union all select 5001,'井A',1,100
union all select 5002,'井B',1,105
)select a.id as id1,a.mc as mc1 ,a.vl as vl1,b.id as id2,b.mc as mc2,b.vl as v12
from (select *,rn=ROW_NUMBER() over(order by id)
from (select id,mc,SUM(vl) as vl from cte where item=0 group by id,mc)t1)A
LEFT JOIN
(select *,rn=ROW_NUMBER() over(order by id)
from (select id,mc,SUM(vl) as vl from cte where item=1 group by id,mc)t1)B
on a.rn=b.rn /*
id1 mc1 vl1 id2 mc2 v12
2001 车间1 4600 5001 井A 188
2002 车间2 5400 5002 井B 195
2003 车间3 5500 NULL NULL NULL
*/