sql - Splitting a row into 2 rows based on value present in a column

Question

I got a requirement to split one record into two records based on values in a column.

Sample Input:

+---------+-----------+--------+--------------+------------+------------------+
| REC_KEY | SURR_KEY  | REC_YR | MANUFACTURER | MODEL_NAME |    ENG_POWER     |
+---------+-----------+--------+--------------+------------+------------------+
|   12345 | 615099386 |   2003 | BMW          | Z8         | 4.8L V8; 4.9L V8 |
+---------+-----------+--------+--------------+------------+------------------+

Sample Output:

+---------+-----------+--------+--------------+------------+-----------+
| REC_KEY | SURR_KEY  | REC_YR | MANUFACTURER | MODEL_NAME | ENG_POWER |
+---------+-----------+--------+--------------+------------+-----------+
|   12345 | 615099386 |   2003 | BMW          | Z8         | 4.8L V8   |
|   12345 | 615099386 |   2003 | BMW          | Z8         | 4.9L V8   |
+---------+-----------+--------+--------------+------------+-----------+

Is there any way to implement this?

question from:https://stackoverflow.com/questions/66064572/splitting-a-row-into-2-rows-based-on-value-present-in-a-column

Answer 1

You can split value of that column by ; character through use of REGEXP_SUBSTR(), while counting the number of that characters by REGEXP_COUNT() function

 SELECT rec_key, surr_key, rec_yr, manufacturer, model_name, 
        TRIM( REGEXP_SUBSTR(eng_power,'[^;]+',1,level) ) AS eng_power 
   FROM tab 
CONNECT BY level <= REGEXP_COUNT(eng_power,';') + 1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR rec_key = rec_key

Demo