时间戳

首页 > 解决方案 > Oracle 透视查询

oracle - Oracle 透视查询

问题描述

我在表格中有以下格式的数据。

Date           Country  City         Sales
----------------------------------------------
01/01/2019  USA Alabama       1
01/01/2019  USA Alaska        2
01/01/2019  USA Kansas        3
01/01/2019  USA  Maryland     4
01/01/2019  UK  London        5
01/01/2019  UK  Edinburgh     6
02/01/2019  USA Alabama       7
02/01/2019  USA Alaska        8
02/01/2019  USA Kansas        9
02/01/2019  USA  Maryland     10
02/01/2019  UK  London        11
02/01/2019  UK  Edinburgh     12
03/01/2019  USA Alabama       13
03/01/2019  USA Alaska        14
03/01/2019  USA Kansas        15
03/01/2019  USA Maryland     16
03/01/2019  UK  London        17
03/01/2019  UK  Edinburgh     18

我想得到如下的输出

            USA     USA     USA     USA        UK        UK
Date        Alabama Alaska  Kansas  Maryland   London   Edinburgh
-----------------------------------------------------------------------
01/01/2019  1       2       3       4          5        6
02/01/2019  7       8       9       10         11       12
03/01/2019  13      14      15      16         17       18

标签： oracle

解决方案

在和PIVOT的组合上使用 a ：countrycity

甲骨文设置：

CREATE TABLE table_name ( DT, Country, City, Sales ) AS
  SELECT DATE '2019-01-01', 'USA', 'Alabama',    1 FROM DUAL UNION ALL
  SELECT DATE '2019-01-01', 'USA', 'Alaska',     2 FROM DUAL UNION ALL
  SELECT DATE '2019-01-01', 'USA', 'Kansas',     3 FROM DUAL UNION ALL
  SELECT DATE '2019-01-01', 'USA', 'Maryland',   4 FROM DUAL UNION ALL
  SELECT DATE '2019-01-01', 'UK',  'London',     5 FROM DUAL UNION ALL
  SELECT DATE '2019-01-01', 'UK',  'Edinburgh',  6 FROM DUAL UNION ALL
  SELECT DATE '2019-01-02', 'USA', 'Alabama',    7 FROM DUAL UNION ALL
  SELECT DATE '2019-01-02', 'USA', 'Alaska',     8 FROM DUAL UNION ALL
  SELECT DATE '2019-01-02', 'USA', 'Kansas',     9 FROM DUAL UNION ALL
  SELECT DATE '2019-01-02', 'USA', 'Maryland',  10 FROM DUAL UNION ALL
  SELECT DATE '2019-01-02', 'UK',  'London',    11 FROM DUAL UNION ALL
  SELECT DATE '2019-01-02', 'UK',  'Edinburgh', 12 FROM DUAL UNION ALL
  SELECT DATE '2019-01-03', 'USA', 'Alabama',   13 FROM DUAL UNION ALL
  SELECT DATE '2019-01-03', 'USA', 'Alaska',    14 FROM DUAL UNION ALL
  SELECT DATE '2019-01-03', 'USA', 'Kansas',    15 FROM DUAL UNION ALL
  SELECT DATE '2019-01-03', 'USA', 'Maryland',  16 FROM DUAL UNION ALL
  SELECT DATE '2019-01-03', 'UK',  'London',    17 FROM DUAL UNION ALL
  SELECT DATE '2019-01-03', 'UK',  'Edinburgh', 18 FROM DUAL;

查询：

SELECT *
FROM   table_name
PIVOT  ( SUM( sales ) FOR ( Country, City ) IN (
  ( 'USA', 'Alabama'   ) AS USA_Alabama,
  ( 'USA', 'Alaska'    ) AS USA_Alaska,
  ( 'USA', 'Kansas'    ) AS USA_Kansas,
  ( 'USA', 'Maryland'  ) AS USA_Maryland,
  ( 'UK',  'London'    ) AS UK_London,
  ( 'UK',  'Edinburgh' ) AS UK_Edinburgh
) )
ORDER BY dt

输出：

DT | 美国_阿拉巴马 | 美国_阿拉斯加 | 美国_堪萨斯州 | 美国_马里兰 | 英国_伦敦 | 英国_爱丁堡
:-------- | ----------: | ---------: | ---------: | ------------: | --------: | ------------:
19 年 1 月 1 日 | 1 | 2 | 3 | 4 | 5 | 6
2019 年 1 月 2 日 | 7 | 8 | 9 | 10 | 11 | 12
2019 年 1 月 3 日 | 13 | 14 | 15 | 16 | 17 | 18

db<>在这里摆弄

推荐阅读