oracle数据库表处理纵表转横表,oracle 横表转竖表 求解解决办法

SQL codeSELECT 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

比如说你的表是ASQL codeSELECT 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