oracle转置的2种方法
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;
第一个是以前写的,第二个其实更简单,前几天写的