Oracle SQL - New table with transpose column to row

Question

Currently I have a Oracle table called 'test' and have below data (3 columns):

KEY GROUP VALUE 
1   col_1 789
1   col_2 12345
1   col_3 525
1   col_4 ABCD
2   col_1 abc
2   col_2 ds
2   col_3 fg
2   col_4 1234
2   col_5 777

I want to create a new table based on the table 'test' but want the column transpose to row, below is what I expect:

KEY COL1  COL2   COL3  COL4   COL5
1   789   12345  525   ABCD  (null)
2   abc   ds     fg    1234   777

Not every 'Key' has all the 'GROUP' value in the 'test' table. For example, Key 2 has 5 Groups but Key 1 only has 4, I need the new table structure to have all the Groups available for any give Key. If a Key does not have Group value but other Key does, leave the cell as Null.

Can anyone write me a sample query? Thanks!

Possible duplicate of How to convert Rows to Columns in Oracle? — Sam M

D-Shih D-Shih · Accepted Answer · 2018-10-31T22:06:25

You can try to use ROW_NUMBER make row number then do condition aggregate function to make it.

CREATE TABLE T(
   "KEY" INT,
   "GROUP" VARCHAR(50),
   "VALUE" VARCHAR(50)
);


INSERT INTO T VALUES (1,'col_1','789');
INSERT INTO T VALUES (1,'col_2','12345');
INSERT INTO T VALUES (1,'col_3','525');
INSERT INTO T VALUES (1,'col_4','ABCD');
INSERT INTO T VALUES (2,'col_1','abc');
INSERT INTO T VALUES (2,'col_2','ds');
INSERT INTO T VALUES (2,'col_3','fg');
INSERT INTO T VALUES (2,'col_4','1234');
INSERT INTO T VALUES (2,'col_5','777');

Query 1:

SELECT "KEY",
       MAX(CASE WHEN rn = 1 THEN "VALUE" END) COL1,
       MAX(CASE WHEN rn = 2 THEN "VALUE" END) COL2,
       MAX(CASE WHEN rn = 3 THEN "VALUE" END) COL3,
       MAX(CASE WHEN rn = 4 THEN "VALUE" END) COL4,
       MAX(CASE WHEN rn = 5 THEN "VALUE" END) COL5
 FROM (
    SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY "KEY" ORDER BY "GROUP") rn
    FROM T t1
) t1
GROUP BY "KEY"

Results:

| KEY | COL1 |  COL2 | COL3 | COL4 |   COL5 |
|-----|------|-------|------|------|--------|
|   1 |  789 | 12345 |  525 | ABCD | (null) |
|   2 |  abc |    ds |   fg | 1234 |    777 |

Oracle SQL - New table with transpose column to row

1 Answers