oracle 查询转置,oracle转置的2种方法-Oracle

oracle转置的2种方法

下面是2种方法

方法1:SYS_CONNECT_BY_PATH , ROW_NUMBER() OVER(PARTITION BY  ..  ORDER BY ..)  , START WITH , CONNECT BY PRIOR 组合使用

方法2:wmsys.wm_concat

例:

table1 中 1个col1对应多个col2,下面我们需要把col2转置如col2字段值为

2

3

4

需要变为 2-3-4 这样的格式,并且col2值是不可枚举的有上千或上万种,这样其他有些通过decode方式的转置就不能实现

使用方法1

Sql代码

SELECT TT.col1,

‘-‘ || ‘:’ ||

TO_CHAR(SUBSTR(MAX(SYS_CONNECT_BY_PATH(TT.col2, ‘-‘)), 2)) M

–这里是为了截取掉 SYS_CONNECT_BY_PATH 在第一个值前加的”-”

FROM (SELECT T.col1,

T.col2,

T.col1 + ROW_NUMBER() OVER(PARTITION BY T.col1 ORDER BY T.col2) RN,

ROW_NUMBER() OVER(PARTITION BY T.col1 ORDER BY T.col2) RM

–上面2行用了2次 ROW_NUMBER() 是因为 col1是累加的值所以一个 T.col1 + ROW_NUMBER() 是为了区别不同的分组,ROW_NUMBER() 这个是为了设置递归的起始值,但对于不同的分组都会有这个值”1″所以需要使用2个

FROM table1 T

WHERE /*T.col1 = TO_NUMBER(‘1013010875782363’)*/) TT  –注释的部分是我测试用的

START WITH RM = 1

CONNECT BY PRIOR RN + 1 = RN

GROUP BY TT.col1 ;

使用方法2

Sql代码

select substr(tt.co, 1, length(tt.co) – 1), –去结尾”-”

tt.col1

from (select t.col1, replace(wmsys.wm_concat(t.col2 || ‘-‘), ‘,’, null) co –去掉”,”

from table1 t

–WHERE T.col1 = TO_NUMBER(‘1013010875782363’)

group by t.col1) tt;

第一个是以前写的,第二个其实更简单,前几天写的