CODEWITHSUNDEEP
sqloracle-database
Arun Solomon
Arun Solomon

Reputation: 421

Show table data single in a row

Need to know the output in Oracle sql

Country              Code
USA                  23, 24, 25, 30
Canada               40, 50, 70

Output to be displayed like this

Country            Code
USA                 23
USA                 24
USA                 25
USA                 30
Canada              40
Canada              50
Canada              70

Upvotes: 1

Views: 52

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use connect by with instr get , group number to level, then use regexp_substr get the string.

Oracle 11g R2 Schema Setup:

CREATE TABLE T(
   Country varchar(50),
   Code varchar(50)
);


INSERT INTO T VALUES ('USA','23, 24, 25, 30');
INSERT INTO T VALUES ('Canada','40, 50, 70');

Query 1:

select distinct Country,trim(regexp_substr(Code, '[^,]+', 1, level)) Code
FROM T
connect by instr(Code, ',', 1, level - 1)  > 0
ORDER BY Country desc

Results:

| COUNTRY | CODE |
|---------|------|
|     USA |   23 |
|     USA |   24 |
|     USA |   25 |
|     USA |   30 |
|  Canada |   40 |
|  Canada |   50 |
|  Canada |   70 |

Upvotes: 1

Related Questions