oracle sql转置,Oracle SQL函数pivot、unpivot转置函数实现行转列、列转行

函数PIVOT、UNPIVOT转置函数实现行转列、列转行，效果如下图所示：1.PIVOT为行转列，从图示的左边到右边2.UNPIVOT为列转行，从图示的右边到左边3.左边为纵表，结构简单，易扩展4.右边为横表，展示清晰，方便查询5.很多时候业务表为纵表，但是统计分析需要的结果如右边的横表，这时候就需要用到转置函数了

示例图表：

Pivot语法：SELECT....FROM PIVOT

(

aggregate-function()FOR IN (, ,..., )

)AS

WHERE.....

注意：FOR 这个是不支持表达式的，如果需要，请通过子查询或者视图先预处理。

Pivot

例子1：先构造一个子查询，然后根据CHANNEL列进行转置，源表sales_view里面可能有很多列，不需要列先通过子查询过滤掉再进行转置。

另外转置后的列指定了别名，值是对amount_sold列的汇总。SELECT * FROM(SELECTproduct, channel, amount_soldFROMsales_view

) S PIVOT (SUM(amount_sold)FOR CHANNEL IN (3 AS DIRECT_SALES, 4 ASINTERNET_SALES,5 AS CATALOG_SALES, 9 ASTELESALES))ORDER BYproduct;

PRODUCT DIRECT­_SALES INTERNET_SALES CATALOG_SALES TELESALES---------------------- ------------ -------------- ------------- ---------

...

Internal 6X CD-ROM 229512.97 26249.55Internal 8X CD-ROM 286291.49 42809.44Keyboard Wrist Rest200959.84 38695.36 1522.73...

例子2：基于多列进行转置，下面例子是基于channel、quarter两列进行转置SELECT *

FROM(SELECTproduct, channel, quarter, quantity_soldFROMsales_view

) PIVOT (SUM(quantity_sold)FOR (channel, quarter) IN((5, '02') ASCATALOG_Q2,

(4, '01') ASINTERNET_Q1,

(4, '04') ASINTERNET_Q4,

(2, '02') ASPARTNERS_Q2,

(9, '03') ASTELE_Q3

)

);

PRODUCT CATALOG_Q2 INTERNET_Q1 INTERNET_Q4 PARTNERS_Q2 TELE_Q3------- ---------- ----------- ----------- ----------- -------

...

Bounce347 632 954...

Smash Up Boxing129 280 560...

Comic Book Heroes47 155 275...

例子3：对多列的值进行汇总计算，以下是基于channel例进行转置，然后对amount_sold和quantity_sold两列进行合计运算SELECT *

FROM(SELECTproduct, channel, amount_sold, quantity_soldFROMsales_view

) PIVOT (SUM(amount_sold) ASsums,SUM(quantity_sold) ASsumqFOR channel IN (5, 4, 2, 9)

)ORDER BYproduct;

PRODUCT 5_SUMS 5_SUMQ 4_SUMS 4_SUMQ 2_SUMS 2_SUMQ 9_SUMS 9_SUMQ------------- ------ ------ ------ ------ ------ ------ ------ ------

O/S Doc Set English 142780.36 3081 381397.99 8044 6028.66 134O/S Doc Set French 55503.58 1192 132000.77 2782...

Unpivot

unpivot是pivot的相反操作，进行的是列转行

例子1：先看源表结构，for子句指定将(Q1_SUMQ, Q2_SUMQ, Q3_SUMQ, Q4_SUMQ)这4列转置为行，

for子句之前的quantity_sold是4列转置后的列名，

decode还定义了每列转置为行后新标示列的值，这个等下看第2个例子可以看到，也可以在in 子句后面加 as指定别名。

UNPIVOT INCLUDE NULLS 指定空值也进行转置，如果是EXCLUDE NULLS 将忽略空值。SELECT *

FROMpivotedTableORDER BYproduct;

PRODUCT Q1_SUMQ Q1_SUMA Q2_SUMQ Q2_SUMA Q3_SUMQ Q3_SUMA Q4_SUMQ Q4_SUMA--------------- ------- ------- ------- -------- ------- -------- ------- ---------

1.44MB External 6098 58301.33 5112 49001.56 6050 56974.3 5848 55341.28128MB Memory1963 110763.63 2361 132123.12 3069 170710.4 2832 157736.6

17" LCD 1492 1812786.94 1387 1672389.06 1591 1859987.66 1540 1844008.11

SELECT product, DECODE(quarter, 'Q1_SUMQ', 'Q1', 'Q2_SUMQ', 'Q2', 'Q3_SUMQ', 'Q3','Q4_SUMQ', 'Q4') ASquarter, quantity_soldFROMpivotedTable

UNPIVOT INCLUDE NULLS

(quantity_soldFOR quarter IN(Q1_SUMQ, Q2_SUMQ, Q3_SUMQ, Q4_SUMQ))ORDER BYproduct, quarter;

PRODUCT QUARTER QUANTITY_SOLD------- -- -------------

1.44MB External 3.5" Diskette Q1 6098

1.44MB External 3.5" Diskette Q2 5112

1.44MB External 3.5" Diskette Q3 6050

1.44MB External 3.5" Diskette Q4 5848128MB Memory Card Q11963128MB Memory Card Q22361128MB Memory Card Q33069128MB Memory Card Q42832...

例子2：转置多列的情况SELECTproduct, quarter, quantity_sold, amount_soldFROMpivotedTable

UNPIVOT INCLUDE NULLS

(

(quantity_sold, amount_sold)FOR quarter IN ((Q1_SUMQ, Q1_SUMA) AS 'Q1', (Q2_SUMQ, Q2_SUMA) AS 'Q2', (Q3_SUMQ, Q3_SUMA) AS 'Q3', (Q4_SUMQ, Q4_SUMA) AS 'Q4'))ORDER BYproduct, quarter;

PRODUCT QU QUANTITY_SOLD AMOUNT_SOLD----------------------------- -- ------------- ------------

1.44MB External 3.5" Diskette Q1 6098 58301.33

1.44MB External 3.5" Diskette Q2 5112 49001.56

1.44MB External 3.5" Diskette Q3 6050 56974.3

1.44MB External 3.5" Diskette Q4 5848 55341.28128MB Memory Card Q11963 110763.63128MB Memory Card Q22361 132123.12128MB Memory Card Q33069 170710.4128MB Memory Card Q42832 157736.6总结，基本上按照语法套用即可，注意将源表非相关列先过滤掉，可是是子查询，也可以是视图。---------------------

作者：Seandba

来源：CSDN

原文：https://blog.csdn.net/seandba/article/details/72730657?utm_source=copy

版权声明：本文为博主原创文章，转载请附上博文链接！