oracle 横表转竖表 求解解决办法
oracle 横表转竖表 求解
A1 A2 A3 A4 A5
21 1 2 3 4
22 5 6 7 8
23 9 10 11 12
24 13 14 15 16
有这样一个表,
想得到
21 22 23 24
1 5 9 13
2 6 10 14
3 7 11 15
4 8 12 16
这样的数据。不用游标,用sql直接写该怎么写?
------解决方案--------------------
晕我想不出别的方法来,先贴我的,比如你的表名为A
A1 A2 A3 A4 A5
21 1 2 3 4
22 5 6 7 8
23 9 10 11 12
24 13 14 15 16
有这样一个表,
想得到
21 22 23 24
1 5 9 13
2 6 10 14
3 7 11 15
4 8 12 16
这样的数据。不用游标,用sql直接写该怎么写?
------解决方案--------------------
晕我想不出别的方法来,先贴我的,比如你的表名为A
- SQL code
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.GB1 UNION ALL SELECT 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
- SQL code
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.GB1 UNION ALL SELECT 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.GB1 UNION ALL SELECT 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.GB1 UNION ALL SELECT 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.GB1 UNION ALL SELECT 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