CODEWITHSUNDEEP
sqloracle-databasepivot
Ewaver
Ewaver

Reputation: 1191

Oracle SQL PIVOT with multiple sum columns

I have a table with like this...

create table my_table_for_pivot (month date, country varchar2(40),metric1, metric2);

insert into my_table_for_pivot(month, country, metric1, metric2) values ('01-jan-2013', 'usa','100','75');
insert into my_table_for_pivot(month, country, metric1, metric2) values ('01-jan-2013', 'argentina','24','10');
insert into my_table_for_pivot(month, country, metric1, metric2) values ('01-mar-2013', 'brazil','60','15');
insert into my_table_for_pivot(month, country, metric1, metric2) values ('01-feb-2013', 'usa','111','90');
insert into my_table_for_pivot(month, country, metric1, metric2) values ('01-feb-2013', 'argentina','15','20');
insert into my_table_for_pivot(month, country, metric1, metric2) values ('01-feb-2013', 'brazil','75','30');
insert into my_table_for_pivot(month, country, metric1, metric2) values ('01-mar-2013', 'usa','105','70');
insert into my_table_for_pivot(month, country, metric1, metric2) values ('01-mar-2013', 'argentina','27','23');
insert into my_table_for_pivot(month, country, metric1, metric2) values ('01-mar-2013', 'brazil','53,'28');

My goal is to pivot the table so that the months are along the column headers, but each metric is listed on its own row. My users will be selecting country in a dropdown and the result will recalculate based on the item dropdown (like :p20_country) The resultant table would look someting like this...

users choose usa

Metric  jan13  feb13 mar13
metric1 100     111   105
metric2 75      90    70

my pivot attempts have not worked as I don;t know how to get the metric name on the row level.

select *
from (
select
country,
month,
metric1,
metric2
from my_table_for_pivot)
pivot (
sum(metric1) as mo1,sum(metric2) as mo2 for month IN
('01-JAN-14' AS JAN_14,
 '01-FEB-14' AS FEB_14,
 '01-MAR-14' AS MAR_14))
where country = 'usa'
;

I did try to use SQLfiddle but could not get to work for this example..would very much appreciate any help or advice.

Upvotes: 0

Views: 11348

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

I'm pretty sure there are more optimal ways to write this query, but this is one way; merge the metric values into a single column and add a name column which is not mentioned in the pivot part. This will create a row per name;

SELECT *
FROM (
  SELECT country, month, metric1 metric, 'metric1' metric_name
  FROM my_table_for_pivot
  UNION ALL
  SELECT country, month, metric2 metric, 'metric2' metric_name
  FROM my_table_for_pivot
)
PIVOT (
  SUM(metric) as month for month IN
('01-JAN-2013' AS JAN_13,
 '01-FEB-2013' AS FEB_13,
 '01-MAR-2013' AS MAR_13))
WHERE country = 'usa'
;

An SQLfiddle to test with.

Upvotes: 2

Related Questions