sql server查询，三列转为六列显示
表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 5500 sql?server
[解决办法]
忘记了，还有统计：
;with cte(id,mc,item,vl) as
(
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
/*
id1mc1vl1id2mc2v12
2001车间146005001井A188
2002车间254005002井B195
2003车间35500NULLNULLNULL
*/
