0

I have a table with the below structure:

enter image description here

I would like to retrieve the results using sql in the below format

enter image description here

I am new to SQL and can't figure out how to go about it. Is this possible without using procedures? How do I go achieve this? (the actual data size is huge and I have given only a snapshot here)

Chillax
  • 4,418
  • 21
  • 56
  • 91
  • 2
    Possible duplicate of [Oracle SQL PIVOT Table](http://stackoverflow.com/questions/20551262/oracle-sql-pivot-table) – krokodilko May 14 '16 at 20:09
  • 1
    Another answers: http://stackoverflow.com/questions/7730111/pivoting-rows-into-columns-dynamically-in-oracle ,http://stackoverflow.com/questions/23700034/pivot-in-oracle-11g, http://stackoverflow.com/questions/13410464/pivot-on-oracle-10g, http://stackoverflow.com/questions/23939244/using-pivot-on-multiple-columns-of-an-oracle-row, http://stackoverflow.com/questions/3120835/how-to-pivot-rows-into-columns-custom-pivoting, http://stackoverflow.com/questions/10929108/rotate-pivot-table-with-aggregation-in-oracle, http://stackoverflow.com/questions/11987067/pivoting-of-data-using-two-columns – krokodilko May 14 '16 at 20:12
  • Note that this request includes row and column totals - which of those other questions cover that? The first few I checked don't. –  May 14 '16 at 21:23

1 Answers1

1

Part of it is pivoting. Totals by row and column (and really, even the pivoting) should be done in your reporting application, not in SQL. If you insist on doing it in SQL, there are fancier ways, but something like the silly query below will suffice.

with test_data (city, yr, ct) as (
        select 'Tokyo' , 2016,  2 from dual union all
        select 'Mumbai', 2013,  3 from dual union all
        select 'Mumbai', 2014,  5 from dual union all
        select 'Dubai' , 2011,  5 from dual union all
        select 'Dubai' , 2015, 15 from dual union all
        select 'Dubai' , 2016,  8 from dual union all
        select 'London', 2011, 16 from dual union all
        select 'London', 2012, 22 from dual union all
        select 'London', 2013,  4 from dual union all
        select 'London', 2014, 24 from dual union all
        select 'London', 2015, 13 from dual union all
        select 'London', 2016,  5 from dual
     ),
     test_with_totals as (
        select          city,   yr, ct       from test_data                  union all
        select          city, 9999, sum(ct)  from test_data   group by city  union all
        select 'Grand Total', yr  , sum(ct)  from test_data   group by yr    union all 
        select 'Grand Total', 9999, sum(ct)  from test_data
     )
select * from test_with_totals
pivot ( sum (ct) for yr in (2011, 2012, 2013, 2014, 2015, 2016, 9999 as "Total"))
order by "Total";

Result:

CITY              2011       2012       2013       2014       2015       2016      Total
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Tokyo                                                                       2          2
Mumbai                                     3          5                                8
Dubai                5                                          15          8         28
London              16         22          4         24         13          5         84
Grand Total         21         22          7         29         28         15        122