原文地址:http://blog.csdn.net/seandba/article/details/72730657
函数PIVOT、UNPIVOT转置函数实现行转列、列转行,效果如下图所示:
1.PIVOT为行转列,从图示的左边到右边
2.UNPIVOT为列转行,从图示的右边到左边
3.左边为纵表,结构简单,易扩展
4.右边为横表,展示清晰,方便查询
5.很多时候业务表为纵表,但是统计分析需要的结果如右边的横表,这时候就需要用到转置函数了
示例图表:
Pivot语法:
- SELECT ....
- FROM <table-expr>
- PIVOT
- (
- aggregate-function(<column>)
- FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
- ) AS <alias>
- WHERE .....
注意:
- FOR <pivot-column>
这个是不支持表达式的,如果需要,请通过子查询或者视图先预处理。
Pivot
例子1:先构造一个子查询,然后根据CHANNEL列进行转置,源表sales_view里面可能有很多列,不需要列先通过子查询过滤掉再进行转置。
另外转置后的列指定了别名,值是对amount_sold列的汇总。
- SELECT * FROM
- (SELECT product, channel, amount_sold
- FROM sales_view
- ) S PIVOT (SUM(amount_sold)
- FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,
- 5 AS CATALOG_SALES, 9 AS TELESALES))
- ORDER BY product;
- PRODUCT DIRECT_SALES INTERNET_SALES CATALOG_SALES TELESALES
- ---------------------- ------------ -------------- ------------- ---------
- ...
- Internal 6X CD-ROM 229512.97 26249.55
- Internal 8X CD-ROM 286291.49 42809.44
- Keyboard Wrist Rest 200959.84 38695.36 1522.73
- ...
例子2:基于多列进行转置,下面例子是基于channel、quarter两列进行转置
- SELECT *
- FROM
- (SELECT product, channel, quarter, quantity_sold
- FROM sales_view
- ) PIVOT (SUM(quantity_sold)
- FOR (channel, quarter) IN
- ((5, '02') AS CATALOG_Q2,
- (4, '01') AS INTERNET_Q1,
- (4, '04') AS INTERNET_Q4,
- (2, '02') AS PARTNERS_Q2,
- (9, '03') AS TELE_Q3
- )
- );
- PRODUCT CATALOG_Q2 INTERNET_Q1 INTERNET_Q4 PARTNERS_Q2 TELE_Q3
- ------- ---------- ----------- ----------- ----------- -------
- ...
- Bounce 347 632 954
- ...
- Smash Up Boxing 129 280 560
- ...
- Comic Book Heroes 47 155 275
- ...
例子3:对多列的值进行汇总计算,以下是基于channel例进行转置,然后对amount_sold和quantity_sold两列进行合计运算
- SELECT *
- FROM
- (SELECT product, channel, amount_sold, quantity_sold
- FROM sales_view
- ) PIVOT (SUM(amount_sold) AS sums,
- SUM(quantity_sold) AS sumq
- FOR channel IN (5, 4, 2, 9)
- )
- ORDER BY product;
- 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 134
- O/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 *
- FROM pivotedTable
- ORDER BY product;
- 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.28
- 128MB Memory 1963 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') AS quarter, quantity_sold
- FROM pivotedTable
- UNPIVOT INCLUDE NULLS
- (quantity_sold
- FOR quarter IN (Q1_SUMQ, Q2_SUMQ, Q3_SUMQ, Q4_SUMQ))
- ORDER BY product, 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 5848
- 128MB Memory Card Q1 1963
- 128MB Memory Card Q2 2361
- 128MB Memory Card Q3 3069
- 128MB Memory Card Q4 2832
- ...
例子2:转置多列的情况
- SELECT product, quarter, quantity_sold, amount_sold
- FROM pivotedTable
- 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 BY product, 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.28
- 128MB Memory Card Q1 1963 110763.63
- 128MB Memory Card Q2 2361 132123.12
- 128MB Memory Card Q3 3069 170710.4
- 128MB Memory Card Q4 2832 157736.6
总结,基本上按照语法套用即可,注意将源表非相关列先过滤掉,可是是子查询,也可以是视图。
最后试试解决这个问题吧,看你是否真的懂了!
http://blog.csdn.net/seandba/article/details/72629724
以上内容均来自Oracle11g官方文档,我只是搬运工。。。
Oracle® Database Data Warehousing Guide
11g Release 2 (11.2)
E25554-01