CODEWITHSUNDEEP
sqloracle-databasepivot
sandro98
sandro98

Reputation: 29

How to Convert Rows to Columns in Oracle using pivot

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

;

Upvotes: 0

Views: 103

Answers (2)

vishnudattan
vishnudattan

Reputation: 476

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()

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 1

Related Questions