How to Convert Rows to Columns in Oracle using pivot

Question

I have the table

id  year        quarter     total
1   2016        1           100
1   2016        2           200
1   2016        3           300
1   2016        4           400
2   2016        1           100
2   2016        2           200

I want to get the output:

id  year    total1  total2  total3  total4
1   2016    100     200     300     400
2   2016    100     200

I have tried

SELECT year,
       "1"     AS total1,
       "2"     AS total2,
       "3"     AS total3,
       "4"     AS total4
  FROM (SELECT 
               *
          FROM mytable 
               PIVOT (... FOR quarter IN (1, 2, 3, 4))
         where year=2016) a

;

Is this a homework assignment that requires the use of the PIVOT operator? It's not the way I'd go (I'd use conditional aggregation like the posted answers), but if you have to use PIVOT, you have to use PIVOT. — Eric Brandt
– Eric Brandt, Commented Feb 26, 2020 at 14:44
Sorry, I do not want max or sum any values. I simply want to output the value of total in every quarter as a new column — sandro98
– sandro98, Commented Feb 26, 2020 at 15:07
You're not understanding how the code works. Try it out before you dismiss it. — Eric Brandt
– Eric Brandt, Commented Feb 26, 2020 at 15:08
Your query works if you use max(total) or sum(total) instead of dots. Pivot needs aggregation, but in this case it will create rows for each id. The question is what to do if there are two rows for same quarter with same ID. You want sum, max or separate rows? — Ponder Stibbons
– Ponder Stibbons, Commented Feb 26, 2020 at 15:16

Yogesh Sharma · Accepted Answer · 2020-02-26 14:40:57Z

1

You can do aggregation :

SELECT id, year,
       SUM(CASE WHEN quarter = 1 THEN total ELSE 0 END) AS total1,
       SUM(CASE WHEN quarter = 2 THEN total ELSE 0 END) AS total2,
       SUM(CASE WHEN quarter = 3 THEN total ELSE 0 END) AS total3,
       SUM(CASE WHEN quarter = 4 THEN total ELSE 0 END) AS total4
FROM mytable 
GROUP BY id, year;

answered Feb 26, 2020 at 14:40

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vishnudattan · Accepted Answer · 2020-02-26 15:45:31Z

0

Assuming you're looking for the usage of pivot() function, please try below:

    SELECT id,
           year,
           "1_TOTAL" AS total1,
           "2_TOTAL" AS total2,
           "3_TOTAL" AS total3,
           "4_TOTAL" AS total4
      FROM (SELECT * FROM mytable) PIVOT (SUM (total) AS total
                               FOR (quarter)
                               IN (1, 2, 3, 4));

Note: The question doesn't specify whether you're looking for a sum() or max(). For the desired output that you gave, you may use either sum() or max()

answered Feb 26, 2020 at 15:45

vishnudattan

4643 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to Convert Rows to Columns in Oracle using pivot

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related