Home > PL/SQL > [PLSQL] Pivot Query

[PLSQL] Pivot Query

Wednesday, 26 August, 2009 Leave a comment Go to comments

data

mnth CITY SUMMA
1 11 1
1 22 25
1 33 1
2 11 52
2 22 3
2 33 3
3 11 3
3 22 65
3 33 6
WITH t AS
(
SELECT 1 mnth, 11 city, 1 summa FROM dual UNION ALL
SELECT 1 mnth, 22 city, 25 summa FROM dual UNION ALL
SELECT 1 mnth, 33 city, 1 summa FROM dual UNION ALL
SELECT 2 mnth, 11 city, 52 summa FROM dual UNION ALL
SELECT 2 mnth, 22 city, 3 summa FROM dual UNION ALL
SELECT 2 mnth, 33 city, 3 summa FROM dual UNION ALL
SELECT 3 mnth, 11 city, 3 summa FROM dual UNION ALL
SELECT 3 mnth, 22 city, 65 summa FROM dual UNION ALL
SELECT 3 mnth, 33 city, 6 summa FROM dual
) select city
      , sum(decode(mnth, 1, summa, 0)) month1
      , sum(decode(mnth, 2, summa, 0)) month2
      , sum(decode(mnth, 3, summa, 0)) month3
      , sum(sum(summa)) over(partition by city) total
  from t
 group by city;

result

/ mnth mnth mnth mnth12
city 1 2 3 12
11 1 52 3 x
22 25 3 65 y
33 1 3 6 z
Advertisements
Categories: PL/SQL Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: