oracle 横表转竖表 求解
A1 A2 A3 A4 A5
211234
225678
239101112
2413141516
有这样一个表，
想得到
21 22 23 24
1 5 9 13
2 6 10 14
3 7 11 15
4 8 12 16
这样的数据。不用游标，用sql直接写该怎么写？
[解决办法]
晕我想不出别的方法来，先贴我的，比如你的表名为A
SELECT SUM(DECODE(T1.A1, 21, T1.A1, 0)) A1, SUM(DECODE(T1.A1, 22, T1.A1, 0)) A2, SUM(DECODE(T1.A1, 23, T1.A1, 0)) A3, SUM(DECODE(T1.A1, 24, T1.A1, 0)) A4 FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT T1.A1, T2.A2, T3.A3, T4.A4 FROM (SELECT T.A1, ROWNUM RN FROM (SELECT SUM(DECODE(A.A2, 1, A.A2, 0)) A1 FROM A UNION ALL SELECT SUM(DECODE(A.A3, 2, A.A3, 0)) A1 FROM A UNION ALL SELECT SUM(DECODE(A.A4, 3, A.A4, 0)) A1 FROM A UNION ALL SELECT SUM(DECODE(A.A5, 4, A.A5, 0)) A1 FROM A) T) T1, (SELECT T.A2, ROWNUM RN FROM (SELECT SUM(DECODE(A.A2, 5, A.A2, 0)) A2 FROM A UNION ALL SELECT SUM(DECODE(A.A3, 6, A.A3, 0)) A2 FROM A UNION ALL SELECT SUM(DECODE(A.A4, 7, A.A4, 0)) A2 FROM A UNION ALL SELECT SUM(DECODE(A.A5, 8, A.A5, 0)) A2 FROM A) T) T2, (SELECT T.A3, ROWNUM RN FROM (SELECT SUM(DECODE(A.A2, 9, A.A2, 0)) A3 FROM A UNION ALL SELECT SUM(DECODE(A.A3, 10, A.A3, 0)) A3 FROM A UNION ALL SELECT SUM(DECODE(A.A4, 11, A.A4, 0)) A3 FROM A UNION ALL SELECT SUM(DECODE(A.A5, 12, A.A5, 0)) A3 FROM A) T) T3, (SELECT T.A4, ROWNUM RN FROM (SELECT SUM(DECODE(A.A2, 13, A.A2, 0)) A4 FROM A UNION ALL SELECT SUM(DECODE(A.A3, 14, A.A3, 0)) A4 FROM A UNION ALL SELECT SUM(DECODE(A.A4, 15, A.A4, 0)) A4 FROM A UNION ALL SELECT SUM(DECODE(A.A5, 16, A.A5, 0)) A4 FROM A) T) T4 WHERE T1.RN = T2.RN AND T2.RN = T3.RN AND T3.RN = T4.RN
[解决办法]
提供一个思路你看看
oracle10g
比如说你的表是A
SELECT WMSYS.WM_CONCAT(T1.A1) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT WMSYS.WM_CONCAT(T1.A2) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT WMSYS.WM_CONCAT(T1.A3) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT WMSYS.WM_CONCAT(T1.A4) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT WMSYS.WM_CONCAT(T1.A5) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1
