DB2/AS 400 SQL数据透视

mf98qq94 于 2022-11-23 发布在 DB2

关注(0)|答案(1)|浏览(186)

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

|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|   |   |   |   |   |   |   |   |

请求太多？：-）
谢谢支持

db2

来源：https://stackoverflow.com/questions/74136575/db2-as400-sql-pivot

1条答案

按热度按时间

sigwle7e1#

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

| 编码数据|一个|2个|三个|四个|十二|
| - -|- -|- -|- -|- -|- -|
| 艺术品1| 100个|10个||||
| 第二部分|三十|||||
| 第三部分|三十|||五十个||
| 第四部分||四十||||
| 第五章||||六十||
| 总计|一百六十|五十个||第一百一十章||

赞(0）回复(0）举报 2022-11-23

我来回答

DB2/AS 400 SQL数据透视

1条答案

相关问题

热门标签

最新问答