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.

Answer 1

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'));