sql - 根据列中存在的值将一行拆分为 2 行

Question

我需要根据列中的值将一条记录拆分为两条记录。

样本输入：

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

样本输出：

+---------+-----------+--------+--------------+------------+-----------+
| 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   |
+---------+-----------+--------+--------------+------------+-----------+

有什么方法可以实现吗？

标签： sqloraclesubstringoracle12c

Answer 1

;您可以通过使用按字符拆分该列的值，同时按函数REGEXP_SUBSTR()计算该字符的数量REGEXP_COUNT()

 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