要实现下列变换， 请问如何实现
Model status LineA LineB lineC
A FAIL 9 0 0
A PASS 10 0 0
A PASS 12 0 0
B FAIL 0 9 0
B FAIL 0 2 0
B PASS 0 5 0
C FAIL 0 9 0
C FAIL 0 7 0
C PASS 0 6 0
status只有PASS和FAIL两种情况，变换后将Model和status相同的数量相加，然后在将Status 和LineA，Line，lineC进行组合，变成，得到下面的这组数据，请问下SQL语句如何写
Model LineA_PASS lineA_FAIL LineB_PASS LineB_FAIL LineC_PASS Line_CFAIL
A 22 9 0 0 0 0
B 0 0 5 11 0 0
C 0 0 0 0 6 16
------解决方案--------------------
-->try
select Model,
status,
LineA_PASS=(case when Model='A' and status='PASS' then LineA else 0 end),
LineA_FAIL=(case when Model='A' and status='FAIL' then LineA else 0 end),
LineB_PASS=(case when Model='B' and status='PASS' then LineB else 0 end),
LineB_FAIL=(case when Model='B' and status='FAIL' then LineB else 0 end),
LineC_PASS=(case when Model='C' and status='PASS' then LineC else 0 end),
LineC_FAIL=(case when Model='C' and status='FAIL' then LineC else 0 end)
from
(
select Model,status,sum(LineA) LineA,sum(LineB) LineB,sum(LineC) LineC
from 表
group by Model,status
)t
------解决方案--------------------
select Model,
status,
LineA_PASS=sum(case when Model='A' and status='PASS' then LineA else 0 end),
LineA_FAIL=sum(case when Model='A' and status='FAIL' then LineA else 0 end),
LineB_PASS=sum(case when Model='B' and status='PASS' then LineB else 0 end),
LineB_FAIL=sum(case when Model='B' and status='FAIL' then LineB else 0 end),
LineC_PASS=sum(case when Model='C' and status='PASS' then LineC else 0 end),
LineC_FAIL=sum(case when Model='C' and status='FAIL' then LineC else 0 end)
from
tb
------解决方案--------------------
很遗憾,楼上的结果都不对
USE tempdb
GO
CREATE TABLE test
(
Model CHAR(2),
status CHAR(4),
LineA INT ,
LineB INT,
lineC INT
)
INSERT INTO test
SELECT 'A' ,'FAIL', 9, 0 ,0
UNION ALL
SELECT 'A', 'PASS', 10, 0 ,0
UNION ALL
SELECT 'A' ,'PASS', 12 ,0 ,0
UNION ALL
SELECT 'B', 'FAIL', 0, 9 ,0
UNION ALL
SELECT 'B', 'FAIL' ,0 ,2 ,0
UNION ALL
SELECT 'B', 'PASS', 0, 5 ,0
UNION ALL
SELECT 'C', 'FAIL', 0 ,9 ,0
UNION ALL
SELECT 'C', 'FAIL', 0, 7 ,0
UNION ALL
SELECT 'C', 'PASS', 0 ,6 ,0
SELECT Model,