oracle行转列

[youngboy66]

原始数据：


想要实现的结果：

[youngboy66]

竟然没人回复！

[Rotel-刘志东]

多种方法都是可以的。
decode() case when 两种方法都是可以的

[不剪发的Tony老师]

CREATE TABLE t(stage INTEGER,daily INTEGER,monthly INTEGER, dayin INTEGER, dayout INTEGER, dayeff INTEGER);
INSERT INTO t VALUES (1, 200, 1000, 80, 90, 150);
INSERT INTO t VALUES (2, 345, 2345, null, null, null);
INSERT INTO t VALUES (3, 1, 4, null, null, null);
INSERT INTO t VALUES (4, 50, 567, null, 34, null);
INSERT INTO t VALUES (5, 345, 34, 33, 234, 999);
INSERT INTO t VALUES (6, 658, 78, 66, 54, 200);
INSERT INTO t VALUES (7, 88, 99, 9, 33, 87);
INSERT INTO t VALUES (8, 90, 400, 55, 66, 77);

SELECT *
  FROM (SELECT '目标量日' AS "工序", stage, daily
          FROM t
  )
 PIVOT (SUM(daily) FOR (stage) IN (1 AS "1", 2 AS "2", 3 AS "3", 4 AS "4", 5 AS "5", 6 AS "6", 7 AS "7", 8 AS "8"))
 UNION ALL
SELECT *
  FROM (SELECT '目标量月', stage, monthly
          FROM t
  )
 PIVOT (SUM(monthly) FOR (stage) IN (1 AS "1", 2 AS "2", 3 AS "3", 4 AS "4", 5 AS "5", 6 AS "6", 7 AS "7", 8 AS "8"))
 UNION ALL
SELECT *
  FROM (SELECT '当天投入', stage, dayin
          FROM t
  )
 PIVOT (SUM(dayin) FOR (stage) IN (1 AS "1", 2 AS "2", 3 AS "3", 4 AS "4", 5 AS "5", 6 AS "6", 7 AS "7", 8 AS "8"))
 UNION ALL
SELECT *
  FROM (SELECT '当天产出', stage, dayout
          FROM t
  )
 PIVOT (SUM(dayout) FOR (stage) IN (1 AS "1", 2 AS "2", 3 AS "3", 4 AS "4", 5 AS "5", 6 AS "6", 7 AS "7", 8 AS "8"))
 UNION ALL
SELECT *
  FROM (SELECT '当天生产效率', stage, dayeff
          FROM t
  )
 PIVOT (SUM(dayeff) FOR (stage) IN (1 AS "1", 2 AS "2", 3 AS "3", 4 AS "4", 5 AS "5", 6 AS "6", 7 AS "7", 8 AS "8"))
;

工序     |1    |2    |3 |4   |5   |6   |7  |8   |
-------|-----|-----|--|----|----|----|---|----|
目标量日   |200  |345  |1 |50  |345 |658 |88 |90  |
目标量月   |1000 |2345 |4 |567 |34  |78  |99 |400 |
当天投入   |80   |     |  |    |33  |66  |9  |55  |
当天产出   |90   |     |  |34  |234 |54  |33 |66  |
当天生产效率 |150  |     |  |    |999 |200 |87 |77  |

[Rotel-刘志东]

privot() for()in(....)也是可以的。

[Rotel-刘志东]

pivot() for()in(....)也是可以的。