I'm having trouble figuring out how to do something in Oracle. I have this table:

| id  | rownum | code      | gift |
|-----|--------|-----------|------|
|2000 |   1    | Ganymede  | 437  |
|2000 |   2    | Alpha     | 50   |
|2000 |   3    | Ambergris | 600  |

And the client wants it to look like this:

| id | code_1 | gift_1 | code_2 | gift_2 | code_3    | gift_3 |
|----|--------|--------|--------|--------|-----------|--------|
|2000|Ganymede| 437    | Alpha  | 50     | Ambergris | 600    |

I'm not quite sure how to go about doing this using PIVOT.

CodePudding user response：

You can use PIVOT:

SELECT id,
       "1_CODE" AS code_1,
       "1_GIFT" AS gift_1,
       "2_CODE" AS code_2,
       "2_GIFT" AS gift_2,
       "3_CODE" AS code_3,
       "3_GIFT" AS gift_3
FROM   table_name
PIVOT (
  MAX(code) AS code,
  MAX(gift) AS gift
  FOR "ROWNUM" IN (1, 2, 3)
)

or conditional aggregation:

SELECT id,
       MAX(CASE "ROWNUM" WHEN 1 THEN code END) AS code_1,
       MAX(CASE "ROWNUM" WHEN 1 THEN gift END) AS gift_1,
       MAX(CASE "ROWNUM" WHEN 2 THEN code END) AS code_2,
       MAX(CASE "ROWNUM" WHEN 2 THEN gift END) AS gift_2,
       MAX(CASE "ROWNUM" WHEN 3 THEN code END) AS code_3,
       MAX(CASE "ROWNUM" WHEN 3 THEN gift END) AS gift_3
FROM   table_name
GROUP BY id

Which, for the sample data:

CREATE TABLE table_name (id, "ROWNUM", code, gift ) AS
SELECT 2000, 1, 'Ganymede',  437 FROM DUAL UNION ALL
SELECT 2000, 2, 'Alpha',      50 FROM DUAL UNION ALL
SELECT 2000, 3, 'Ambergris', 600 FROM DUAL;

Both output:

ID	CODE_1	GIFT_1	CODE_2	GIFT_2	CODE_3	GIFT_3
2000	Ganymede	437	Alpha	50	Ambergris	600

db<>fiddle here