SQL:如何将合并的行分成单独的行

Question

我有一个数据库表，如下所示：

id   |   check_number        |   amount
1    |   1001]1002]1003      |   200]300]100
2    |   2001]2002           |   500]1000
3    |   3002]3004]3005]3007 |   100]300]600]200

我想把这些记录分成如下几部分：

id   |   check_number    |   amount
1    |   1001            |   200
2    |   1002            |   300
3    |   1003            |   100
.    |     .             |    .
.    |     .             |    .
.    |     .             |    .

如何仅在Oracle和SQL Server中使用SQL来完成此操作？

谢谢,

麦洛

Answer 1

仅在Oracle中，使用按级别连接方法(请参见here)，但有以下几点注意事项：

select rownum, id,
       substr(']'||check_number||']'
              ,instr(']'||check_number||']',']',1,level)+1
              ,instr(']'||check_number||']',']',1,level+1) 
               - instr(']'||check_number||']',']',1,level) - 1) C1VALUE,
       substr(']'||amount||']'
              ,instr(']'||amount||']',']',1,level)+1
              ,instr(']'||amount||']',']',1,level+1) 
               - instr(']'||amount||']',']',1,level) - 1) C2VALUE
    from table
connect by id = prior id and prior dbms_random.value is not null  
      and level <= length(check_number) - length(replace(check_number,']')) + 1


ROWNUM ID  C1VALUE C2VALUE

1      1   1001    200
2      1   1002    300
3      1   1003    100
4      2   2001    500
5      2   2002    1000
6      3   3002    100
7      3   3004    300
8      3   3005    600
9      3   3007    200

本质上，我们使用oracle的层次结构函数展开查询，然后只获得check_number和amount列中每个数据“列”中的数据的子字符串。

主要警告:要转换的数据在两列中必须具有相同数量的“数据元素”，因为我们使用第一列来“计算”要转换的项目的数量。

我已经在11gR2上对此进行了测试。YMMV也取决于DMBS版本。请注意，需要使用“the”运算符，它可以防止oracle进入无限的connect by循环。