DB2/AS400 SQL数据透视

Question

我对透视表有问题。。。。我不知道该怎么办。。。我的表格如下：

|CODART|MONTH|QT  |
|------|-----|----|
|ART1  |1    |100 |
|ART2  |1    |30  |
|ART3  |1    |30  |
|ART1  |2    |10  |
|ART4  |2    |40  |
|ART3  |4    |50  |
|ART5  |4    |60  |

我想按月得到一个汇总表：

|CODART|1  |2  |3  |4  |5  |6  |7  |8  |9  |10 |11 |12 |
|------|---|---|---|---|---|---|---|---|---|---|---|---|
|ART1  |100|10 |   |   |   |   |   |   |   |   |   |   |
|ART2  |30 |   |   |   |   |   |   |   |   |   |   |   |
|ART3  |30 |   |   |50 |   |   |   |   |   |   |   |   | 
|ART4  |   |2  |   |   |   |   |   |   |   |   |   |   |
|ART5  |   |   |   |60 |   |   |   |   |   |   |   |   |
|TOTAL |160|12 |   |110|   |   |   |   |   |   |   |   |

请求太多？：-(

感谢的支持

Answer 1

WITH MYTAB (CODART, MONTH, QT) AS 
(
VALUES 
('ART1', 1, 100)
, ('ART2', 1,  30)
, ('ART3', 1,  30)
, ('ART1', 2,  10)
, ('ART4', 2,  40)
, ('ART3', 4,  50)
, ('ART5', 4,  60)
)
SELECT 
CASE GROUPING (CODART) WHEN 0 THEN CODART ELSE 'TOTAL' END AS CODART
, SUM (CASE MONTH WHEN  1 THEN QT END) AS "1"
, SUM (CASE MONTH WHEN  2 THEN QT END) AS "2"
, SUM (CASE MONTH WHEN  3 THEN QT END) AS "3"
, SUM (CASE MONTH WHEN  4 THEN QT END) AS "4"
---
, SUM (CASE MONTH WHEN 12 THEN QT END) AS "12"
FROM MYTAB T
GROUP BY ROLLUP (T.CODART)
ORDER BY GROUPING (T.CODART), T.CODART

td>总计/td><110>

CODART	1
ART1	100		ART2	30		ART3	30		ART4		40
ART5		160	50