convert column to row in oracle

Question

I have a question, is there any method that convert colum to row. for example,I have a table like this:

CREATE TABLE mytable(u_id, month, offer, revenue) as
SELECT 1, 'January', 'Offer_1', 45  FROM dual
UNION ALL
SELECT 1, 'February','Offer_2', 40  FROM dual
UNION ALL
SELECT 1, 'March'   ,'Offer_1', 35  FROM dual
UNION ALL
SELECT 2, 'January' ,'Offer_2', 40  FROM dual
UNION ALL
SELECT 2, 'February','Offer_3', 40  FROM dual
UNION ALL
SELECT 2, 'March'   ,'Offer_1', 50  FROM dual;

and my expected table is

(There should be one row per user)

u_id	january_offer	january_revenue	february_offer	february_revenue	march_offer	march_revenue
1	Offer_1	45	Offer_2	40	Offer_1	35
2	Offer_2	40	Offer_3	40	Offer_1	50

I tried:

SELECT t1.u_id,
       t1.january_offer,
       t1.january_revenue,
       t2.february_offer,
       t2.february_revenue,
       t3.march_offer,
       t3.march_revenue
  FROM (SELECT u_id, offer AS january_offer, 
                     revenue AS january_revenue
          FROM mytable
         WHERE month = 'January') t1
  LEFT JOIN (SELECT u_id,
                    offer   AS february_offer,
                    revenue AS february_revenue
               FROM mytable t1
              WHERE month = 'February') t2
    ON t1.u_id = t2.u_id
  LEFT JOIN (SELECT u_id, offer AS march_offer, 
                          revenue AS march_revenue
               FROM mytable t1
              WHERE month = 'March') t3
    ON t2.u_id = t3.u_id

but,real, I have lots of data and a long SQL script and this method aggravates my script.

Barbaros Özhan · Answer 1 · 2022-05-02T08:26:55.660

You can use conditional aggregation along with CASE..WHEN expressions such as

SELECT u_id,
       MAX(CASE WHEN month = 'January' THEN offer END) AS january_offer,
       MAX(CASE WHEN month = 'January' THEN revenue END) AS january_revenue,
       MAX(CASE WHEN month = 'February' THEN offer END) AS february_offer,
       MAX(CASE WHEN month = 'February' THEN revenue END) AS february_revenue,
       MAX(CASE WHEN month = 'March' THEN offer END) AS march_offer,
       MAX(CASE WHEN month = 'March' THEN revenue END) AS march_revenue
  FROM t
 GROUP BY u_id

or by using DECODE() function such as

SELECT u_id,
       MAX(DECODE (month , 'January', offer)) AS january_offer,
       MAX(DECODE (month , 'January', revenue)) AS january_revenue,
       MAX(DECODE (month , 'February', offer)) AS february_offer,
       MAX(DECODE (month , 'February', revenue)) AS february_revenue,
       MAX(DECODE (month , 'March', offer)) AS march_offer,
       MAX(DECODE (month , 'March', revenue)) AS march_revenue
  FROM t
 GROUP BY u_id

Demo

score 1 · Answer 2 · answered May 02 '22 at 08:23

An alternative pattern that might be more efficient is to use a case expression:

SELECT t1.u_id
     , CASE month WHEN 'January' then offer END AS january_offer
     , CASE month WHEN 'January' then revenue END AS january_revenue
     , CASE month WHEN 'February' then offer END AS february_offer
     , ...

From there you can use an aggregate function to eliminate null rows:

SELECT u_id
     , MAX(january_offer) AS january_offer
     , MAX(january_revenue) AS january_revenue
     , MAX(february_offer) AS february_offer
     , ...
FROM (  
    SELECT t1.u_id
         , CASE month WHEN 'January' then offer END AS january_offer
         , CASE month WHEN 'February' then offer END AS february_offer
         , ...
) AS t
GROUP BY u_id

In general, this kind of operation is better handled in the presentation layer of the application than in the database layer.

score 1 · Accepted Answer · answered May 02 '22 at 09:08

This is what PIVOT is designed for:

SELECT *
FROM   package
PIVOT (
  MAX(offer) AS offer, MAX(revenue) AS revenue
  FOR month IN ( 'January' AS january, 'February' AS feburary, 'March' AS march )
)

Which, for the sample data:

CREATE TABLE package (U_id, month, offer,revenue) AS
SELECT 1, 'January',  'offer_1', 45 FROM DUAL UNION ALL
SELECT 1, 'February', 'offer_2', 40 FROM DUAL UNION ALL
SELECT 1, 'March',    'offer_1', 35 FROM DUAL UNION ALL
SELECT 2, 'January',  'offer_2', 40 FROM DUAL UNION ALL
SELECT 2, 'February', 'offer_3', 40 FROM DUAL UNION ALL
SELECT 2, 'March',    'offer_1', 50 FROM DUAL;

Outputs:

U_ID JANUARY_OFFER JANUARY_REVENUE FEBURARY_OFFER FEBURARY_REVENUE MARCH_OFFER MARCH_REVENUE

1 offer_1 45 offer_2 40 offer_1 35

2 offer_2 40 offer_3 40 offer_1 50

db<>fiddle here

Though other answers are correct but this is the shortest one. — Ankit Bajpai, May 02 '22 at 11:26

convert column to row in oracle

3 Answers3