Oracle之列转行Unpivot函数_oracle列转行函数unpivot

列转行Unpivot函数用法

unpivot函数：列转行函数：
语法：unpivot(新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名))

如每年的各项指标数据，数据格式如下：

SELECT 
	T.FC_Y AS FORECAST_YEAR, --年份
	T.EPS AS IDX_EPS, -- 指标1
	T.OPER_INC AS IDX_OPER_INC,-- 指标2
	T.NET_PROF AS IDX_NET_PROF,-- 指标3
	T.PE AS IDX_PE-- 指标4
FROM TABLE_NAME T
WHERE T.ID = 1
ORDER BY T.FC_Y ASC;
1
2
3
4
5
6
7
8
9

列转行

SELECT
   T1.FORECAST_YEAR,
   T1.index_id,
   T1.index_value
FROM (
    SELECT 
		T.FC_Y AS FORECAST_YEAR, --年份
		T.EPS AS IDX_EPS, -- 指标1
		T.OPER_INC AS IDX_OPER_INC,-- 指标2
		T.NET_PROF AS IDX_NET_PROF,-- 指标3
		T.PE AS IDX_PE-- 指标4
	FROM TABLE_NAME T
	WHERE T.ID = 1
	ORDER BY T.FC_Y ASC
) unpivot((index_value) for index_id in(IDX_EPS,IDX_OPER_INC,IDX_NET_PROF,IDX_PE)) T1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15