如何根据一个字段是否包含 oracle sql 中的逗号分隔字符串将单行拆分为多行?

Question

我创建了一个函数get_depatment_names该函数在单个返回值中返回逗号分隔的值。 功能输出为：销售，零售，电子。

另一个函数get_location_names以相同的方式返回位置。输出：西约克，郡，兰卡斯

现在，这两个函数，我在我的选择语句中与其他列一起调用。

SELECT dept_id,
date,
get_department_names dept_name,
get_location_names location
status
FROM   department
WHERE dept_id = 1;

输出显示如下：

dept_id ---日期----dept_name -------位置-----状态

1 ---01/01/2018 --- 销售，零售，电子---西约克，郡，兰卡斯-- 活跃

预期产出：

1--01/01/2018 --- 西约克销售--- -- 活跃

1--01/01/2018 --- 零售---郡 -- 活跃

1--01/01/2018 --- 电子---兰卡斯 --主动

我尝试在选择 stmt 中使用带有 connect 的regexp_sub，如下所示，但给出"单行子查询返回多行"的错误。

SELECT dept_id,
date,
(select regexp_substr(get_department_names(id),'[^,]+',1,level) from dual
connect by regexp_substr(get_department_names(id),'[^,]+',1,level) is not null)  dept_name,
(select regexp_substr(get_location_names (id),'[^,]+',1,level) from dual
connect by regexp_substr(get_location_names(id),'[^,]+',1,level) is not null) location
status
FROM   department
WHERE dept_id = 1;

请让我知道如何纠正此问题。

Answer 1

这将是这样的(行 #1 - 4 表示示例数据;您需要的查询从第 #5 行开始(：

SQL> with department (dept_id, datum, dept_name, location, status) as
2    (select 1, date '2018-01-01', 'sales,retail,electronic',
3      'West York,Shire,Lancas', 'active' from dual
4    )
5  select dept_id,
6    datum,
7    regexp_substr(dept_name, '[^,]+', 1, level) dept_name,
8    regexp_substr(location , '[^,]+', 1, level) location,
9    status
10  from department
11  where dept_id = 1
12  connect by level <= regexp_count(dept_name, ',') + 1;
DEPT_ID DATUM      DEPT_NAME       LOCATION        STATUS
---------- ---------- --------------- --------------- ------
1 01/01/2018 sales           West York       active
1 01/01/2018 retail          Shire           active
1 01/01/2018 electronic      Lancas          active
SQL>