sql - 如何在 2 列上旋转
问题描述
date | shop | rank_1 | rank2
-----------+-------+--------+------
01.01.2020 | ZZZ | 1 | 3
01.01.2020 | YYY | 2 | 4
01.01.2020 | DDD | 3 | 5
01.01.2020 | UUU | 4 | 1
01.01.2020 | LLLLL | 5 | 2
表看起来像这样,我想转换,使它看起来像这样:
date | rank_1_1 | rank_1_2 | rank_1_3 | rank_1_4 | rank_1_5 | ...
-----------+----------+----------+----------+----------+----------+------
01.01.2020 | ZZZ | YYY | DDD | UUU | LLLLL | ...
解决方案
Pivot 允许您在一行中显示列数据。您想将2 列转换为行,这有点棘手。我为每个子查询添加了注释来解释我做了什么。理解它的最好方法是再次撕开它并检查会发生什么。
WITH /* sample data */
mydata (thedate, shop, rank_1, rank_2) AS
(
SELECT DATE'2020-01-01', 'ZZZ', 1, 3 FROM DUAL
UNION
SELECT DATE'2020-01-01', 'YYY', 2, 4 FROM DUAL
UNION
SELECT DATE'2020-01-01', 'DDD', 3, 5 FROM DUAL
UNION
SELECT DATE'2020-01-01', 'UUU', 4, 1 FROM DUAL
UNION
SELECT DATE'2020-01-01', 'LLLLL', 5, 2 FROM DUAL
UNION
SELECT DATE'2020-01-02', 'AAA', 5, 2 FROM DUAL
),
/* extract only first column */
my_data_rank1 (thedate, shop, rank_1)
AS
(
SELECT thedate,
shop,
rank_1
FROM mydata
),
/* extract only 2nd column */
my_data_rank2 (thedate, shop, rank_2)
AS
(
SELECT thedate,
shop,
rank_2
FROM mydata
),
/* pivot on 1st column */
rank1_pivot_data AS
(
SELECT * FROM my_data_rank1
PIVOT (MAX(shop) for rank_1 IN ('1' rank_1_1, '2' rank_1_2, '3' rank_1_3 , '4' rank_1_4 , '5' rank_1_5 ))
),
/* pivot on 2nd column */
rank2_pivot_data AS
(
SELECT * FROM my_data_rank2
PIVOT (MAX(shop) for rank_2 IN ('1' rank_2_1, '2' rank_2_2, '3' rank_2_3 , '4' rank_2_4 , '5' rank_2_5 ))
),
/* combine both rows. this will give you 2 rows: one for rank_1 and one for rank_2 */
combined_data (thedate, rank_1_1,rank_1_2,rank_1_3,rank_1_4,rank_1_5,rank_2_1,rank_2_2,rank_2_3,rank_2_4,rank_2_5) AS
(
SELECT thedate, rank_1_1,rank_1_2,rank_1_3,rank_1_4,rank_1_5,null,null,null,null,null
FROM rank1_pivot_data
UNION ALL
SELECT thedate, null,null,null,null,null,rank_2_1,rank_2_2,rank_2_3,rank_2_4,rank_2_5
FROM rank2_pivot_data
),
/* merge the rows into 1 */
combined_data_merged (thedate, rank_1_1,rank_1_2,rank_1_3,rank_1_4,rank_1_5,rank_2_1,rank_2_2,rank_2_3,rank_2_4,rank_2_5)
AS
(
SELECT thedate, MAX(rank_1_1),MAX(rank_1_2),MAX(rank_1_3),MAX(rank_1_4),MAX(rank_1_5),MAX(rank_2_1),MAX(rank_2_2),MAX(rank_2_3),MAX(rank_2_4),MAX(rank_2_5)
FROM combined_data
GROUP BY thedate
)
SELECT * FROM combined_data_merged;
THEDATE RANK_ RANK_ RANK_ RANK_ RANK_ RANK_ RANK_ RANK_ RANK_ RANK_
----------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
01-JAN-2020 ZZZ YYY DDD UUU LLLLL UUU LLLLL ZZZ YYY DDD
02-JAN-2020 AAA AAA
推荐阅读
- visio - 编辑 UML 图表时 MS Visio 2010 挂起
- powershell - delete everything after keyword
- google-bigquery - Check if a dataset has been updated with new table BigQuery
- ntp - Should I specify regional NTP pool or just pool.ntp.org?
- powershell - 希望使用 powershell 从文本文件中删除文本
- python - pip install 2 packages from requirements one after the other
- python - Mayavi - 在 imshow() 图形的数据范围之外选择返回 (0,0,0);如何区分?
- apache-spark - 我的火花程序中的大量阶段
- android-studio - 如何使用 Kotlin 查看方法参数?
- sql - 在 Teradata SQL 中按 id 对列求和