Oracle查询优化-列转行

使用pivot函数

pivot
(
<聚合函数>(要聚合的列)
for <要转换的列> in (要转换的列值 as 要转换成的列名)
)

amount：表示由列转换为行后的数据
month：表示由列转换为行后的列名

select * from REST
pivot (sum(amount) for month in (
'Jan' AS Jan_amount,
'Feb' AS Feb_amount,
'Mar' AS Mar_amount,
'Apr' AS Apr_amount,
'May' AS May_amount,
'Jun' AS Jun_amount,
'Jul' AS Jul_amount,
'Aug' AS Aug_amount,
'Sep' AS Sep_amount,
'Oct' AS Oct_amount,
'Nov' AS Nov_amount,
'Dec' AS Dec_amount
))
unpivot ( amount for month in(Jan_amount,Feb_amount,Mar_amount,Apr_amount,May_amount,Jun_amount,Jul_amount,Aug_amount,Sep_amount,Oct_amount,Nov_amount,Dec_amount));

直接使用unpivot函数
准备数据

CREATE TABLE TEST (
  "ID" NUMBER(12,0) NOT NULL,
  "JAN" VARCHAR2(255 BYTE),
  "FEB" VARCHAR2(255 BYTE),
  "MAR" VARCHAR2(255 BYTE),
  "APR" VARCHAR2(255 BYTE),
  "MAY" VARCHAR2(255 BYTE),
  "JUN" VARCHAR2(255 BYTE),
  "JUL" VARCHAR2(255 BYTE),
  "AUG" VARCHAR2(255 BYTE),
  "SEP" VARCHAR2(255 BYTE),
  "OCT" VARCHAR2(255 BYTE),
  "NOV" VARCHAR2(255 BYTE),
  "DEC" VARCHAR2(255 BYTE)
);
-- 插入数据
INSERT INTO "CERPAWCSADM"."TEST" VALUES ('1', '33', '2', '3', '4', '5', '6', '7', '8', '9', '99', '8', '6');
INSERT INTO "CERPAWCSADM"."TEST" VALUES ('2', '22', '3', '4', '6', '5', '7', '0', '7', '22', '21', '343', '76');
INSERT INTO "CERPAWCSADM"."TEST" VALUES ('3', '88', '3', '4', '5', '7', '9', '7', '2', '2', '231', '56', '78');

执行列转行

SELECT
    * 
FROM TEST
    unpivot ( amount for month in(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC));