Oracle SQL Transpose data

Question

I have a requirement where i have data coming from tables like this

country  total_population  number_of_cities  number_of_airports
US        1,000,000,000          500                25
UK        2,000,000,000          400                20

This is dynamic data. The result i need to show as this

                         US              UK
total_population    1,000,000,000       2,000,000,000
number_of_cities       500               400
number_of_airports      25                20

How can I achieve this ? Any help or pointers are much appreciated.

There are lots of questions with answers for [oracle]+[pivot]. You know the key words, why not check out some of these search results??? — APC
– APC, Commented Jan 23, 2018 at 8:31
This is not exact duplicate. My requirement is different from plain pivot. The question was to basically swap rows with columns. Found my answer after trying many times. The solution involves unpivoting first and then pivoting it back. — ravi
– ravi, Commented Jan 23, 2018 at 20:29
Okay. I have re-opened the question, so you can post your solution as an answer, should you feel like doing so. — APC
– APC, Commented Jan 23, 2018 at 20:34

Kaushik Nayak · Accepted Answer · 2018-01-23 05:41:55Z

1

You could use a combination of MAX, CASE and UNION ALL.

 SELECT 'total_population' metric,
       MAX(CASE
             WHEN country = 'US' THEN total_population
           END)           US,
       MAX(CASE
             WHEN country = 'UK' THEN total_population
           END)           UK
FROM   yourtable
UNION ALL
SELECT 'number_of_cities' metric,
       MAX(CASE
             WHEN country = 'US' THEN number_of_cities
           END)           US,
       MAX(CASE
             WHEN country = 'UK' THEN number_of_cities
           END)           UK
FROM   yourtable
UNION ALL
SELECT 'number_of_airports' metric,
       MAX(CASE
             WHEN country = 'US' THEN number_of_airports
           END)             US,
       MAX(CASE
             WHEN country = 'UK' THEN number_of_airports
           END)             UK
FROM   yourtable;

DEMO

edited Jan 23, 2018 at 5:41

answered Jan 23, 2018 at 5:33

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

APC Over a year ago

And when there are three countries in play? That's a lot of typing. The PIVOT operator is easier to maintain and (arguably) easier to understand.

ravi · Accepted Answer · 2018-01-24 20:04:29Z

0

Here is the solution to the requirement. It basically needed to swap the rows and columns. This can be achived by first unpivoting and then pivoting it back. Here is the query

select *
from (
    select 
        country,
          total_population, number_of_cities, number_of_airports
      FROM yourtable 
      ) unpivot ( val for stats in (total_population, number_of_cities, number_of_airports))
    pivot (sum(val) for country in ('US', 'UK'));

answered Jan 24, 2018 at 20:04

ravi

1,8475 gold badges31 silver badges45 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Transpose data

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related