[PL\SQL] Pivot Query

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

[PLSQL] Pivot Query

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