Oracle Pivot query

Question

I have data in table in below format.

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

I would like get the output of as below

            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

MT0 · Accepted Answer · 2019-09-04 08:51:54Z

Use a PIVOT on the combination of country and city:

Oracle Setup:

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;

Query:

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

Output:

DT        | USA_ALABAMA | USA_ALASKA | USA_KANSAS | USA_MARYLAND | UK_LONDON | UK_EDINBURGH
:-------- | ----------: | ---------: | ---------: | -----------: | --------: | -----------:
01-JAN-19 |           1 |          2 |          3 |            4 |         5 |            6
02-JAN-19 |           7 |          8 |          9 |           10 |        11 |           12
03-JAN-19 |          13 |         14 |         15 |           16 |        17 |           18

db<>fiddle here

Ankit Bajpai · Accepted Answer · 2019-09-04 08:24:19Z

0

You can try below query -

SELECT *
FROM (SELECT Date, Country || ' ' || City, Sales
      FROM YOUR_TABLE) X
PIVOT(
      SALES FOR Country || ' ' || City IN
          ('USA Alabama', 'USA Alaska', 'USA Kansas',
           'USA  Maryland', 'UK  London', 'UK  Edinburgh')
     )

answered Sep 4, 2019 at 8:24

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

1 Comment

MT0 Over a year ago

This is syntactically invalid. The PIVOT needs an aggregation function and you can't have a pivot key like that; you'd need to give it an alias in the sub-query and use that.

Collectives™ on Stack Overflow

Oracle Pivot query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related