oracle行列转换

深藏阁楼爱情的钟 2021-11-11 12:38 355阅读 0赞

**行列转换的几种写法**

**列转行：**

1.最简单的写法，使用关键字PIVOT

语法：SELECT \* FROM TABLENAME PIVOT(MAX(LIST1) FOR LIST2 IN (VALUES1,VALUES2,VALUES3,VALUES4))

例子：

![1681772-20190604143914558-128378717.png][]

实现行列转换效果：

![1681772-20190604143927641-2060773323.png][]

代码：SELECT \* FROM S PIVOT(MAX(AMT) FOR Q IN (1,2,3,4))

2. 使用分析函数load/lag进行行列转换

代码：

SELECT S.Y, S.AMT Q1, S.LD1 Q2, S.LD2 Q3, S.LD3 Q4

FROM (SELECT S.\*,

LEAD(S.AMT, 1) OVER(PARTITION BY S.Y ORDER BY S.Q) LD1,

LEAD(S.AMT, 2) OVER(PARTITION BY S.Y ORDER BY S.Q) LD2,

LEAD(S.AMT, 3) OVER(PARTITION BY S.Y ORDER BY S.Q) LD3

FROM S) S

WHERE S.Q = 1

![1681772-20190604143955307-1044175613.png][]

3. 使用decode函数进行行列转换

代码：

SELECT S.Y,

SUM(DECODE(S.Q, 1, AMT, NULL)) Q1,

SUM(DECODE(S.Q, 2, AMT, NULL)) Q2,

SUM(DECODE(S.Q, 3, AMT, NULL)) Q3,

SUM(DECODE(S.Q, 4, AMT, NULL)) Q4

FROM S

GROUP BY S.Y

![1681772-20190604144008654-1493251728.png][]

4.部分关联

代码：

SELECT S.Y, S.AMT Q1, A.AMT Q2, B.AMT Q3, C.AMT Q4

FROM S,

(SELECT \* FROM S WHERE S.Q = 2) A,

(SELECT \* FROM S WHERE S.Q = 3) B,

(SELECT \* FROM S WHERE S.Q = 4) C

WHERE S.Y = A.Y

AND S.Y = B.Y

AND S.Y = C.Y

AND S.Q=1

![1681772-20190604144032674-279566362.png][]

**行转列：UNPIVOT**

代码：WITH A AS (SELECT \* FROM S PIVOT(MAX(AMT) FOR Q IN (1 Q1,2 Q2,3 Q3,4 Q4))) SELECT \* FROM A UNPIVOT(AMT FOR Q IN (Q1,Q2,Q3,Q4))

![1681772-20190604144051883-2046476242.png][]

转载于:https://www.cnblogs.com/wenxing9415/p/10973495.html

[1681772-20190604143914558-128378717.png]: /images/20211109/5068ca4b254a48968999e4a5983aed58.png
[1681772-20190604143927641-2060773323.png]: /images/20211109/4031ad20b91248cd8549451b77557fcd.png
[1681772-20190604143955307-1044175613.png]: /images/20211109/e09de383c4f34d4ea19f47ec978e64cb.png
[1681772-20190604144008654-1493251728.png]: /images/20211109/fa829c1ce014420ca8edfec07d32d5e4.png
[1681772-20190604144032674-279566362.png]: /images/20211109/ce4ddf1c80ba4f2ca6d482a0b9007b4e.png
[1681772-20190604144051883-2046476242.png]: /images/20211109/5d5bf4f3b3a744aa819f2911751fe7c8.png