WITH CTEA AS(SELECT '1' PRODUCTID,'5' ORDERMONTH,'100' SUBTOTAL
UNION ALL
SELECT '1','6','100'
UNION ALL
SELECT '2','5','200'
UNION ALL
SELECT '2','6','200'
UNION ALL
SELECT '2','7','300'
UNION ALL
SELECT '3','5','400'
UNION ALL
SELECT '3','5','400'
)
SELECT * FROM CTEA--要求结果
PRODUCTID 五月 六月 七月
-------- ---- ---- ----
1 100 100 NULL
2 200 200 300
3 800 NULL NULL
UNION ALL
SELECT '1','6','100'
UNION ALL
SELECT '2','5','200'
UNION ALL
SELECT '2','6','200'
UNION ALL
SELECT '2','7','300'
UNION ALL
SELECT '3','5','400'
UNION ALL
SELECT '3','5','400'
)
select PRODUCTID,max(case when ORDERMONTH='5' then SUBTOTAL else null end)[5],
max(case when ORDERMONTH='6' then SUBTOTAL else null end)[6],
max(case when ORDERMONTH='7' then SUBTOTAL else null end)[7]from CTEA group by PRODUCTID
/*
PRODUCTID 5 6 7
--------- ---- ---- ----
1 100 100 NULL
2 200 200 300
3 400 NULL NULL
/*------------------------
WITH CTEA AS(SELECT '1' PRODUCTID,'5' ORDERMONTH,'100' SUBTOTAL
UNION ALL
SELECT '1','6','100'
UNION ALL
SELECT '2','5','200'
UNION ALL
SELECT '2','6','200'
UNION ALL
SELECT '2','7','300'
UNION ALL
SELECT '3','5','400'
UNION ALL
SELECT '3','5','400'
) SELECT * FROM CTEA pivot (max(SUBTOTAL) for ORDERMONTH in([5],[6] ,[7] )) as pvot
------------------------*/
PRODUCTID 5 6 7
--------- ---- ---- ----
1 100 100 NULL
2 200 200 300
3 400 NULL NULL(3 行受影响)
pivot (sum(SUBTOTAL) ...SUBTOTAL必须为数值型才可以sum,CTEA.SUBTOTAL是字符型,所以要还要先转换。
UNION ALL
SELECT '1','6',100
UNION ALL
SELECT '2','5',200
UNION ALL
SELECT '2','6',200
UNION ALL
SELECT '2','7',300
UNION ALL
SELECT '3','5',400
UNION ALL
SELECT '3','5',400
)
SELECT * FROM CTEA pivot (sum(SUBTOTAL) for ORDERMONTH in([5],[6] ,[7] )) as pvot