PL/SQL中的表转换/字段解析 [英] Table transformation / field parsing in PL/SQL

问题描述

我已经对表格进行了非规范化，例如

I have de-normalized table, something like

CODES

ID  | VALUE
10  | A,B,C
11  | A,B
12  | A,B,C,D,E,F
13  | R,T,D,W,W,W,W,W,S,S

要转换的工作是VALUE中的每个标记将生成新行的位置.示例:

The job is to convert is where each token from VALUE will generate new row. Example:

CODES_TRANS

ID  | VALUE_TRANS
10  | A
10  | B
10  | C
11  | A
11  | B

在PL/SQL中最好的方法是不使用自定义pl/sql软件包，最好是使用纯SQL的最佳方法是什么?

What is the best way to do it in PL/SQL without usage of custom pl/sql packages, ideally with pure SQL?

一种明显的解决方案是通过游标实现它.有什么想法吗?

Obvious solution is to implement it via cursors. Any ideas?

推荐答案

另一种替代方法是使用模型子句:

Another alternative is to use the model clause:

SQL> select id
  2       , value
  3    from codes
  4   model
  5         return updated rows
  6         partition by (id)
  7         dimension by (-1 i)
  8         measures (value)
  9         ( value[for i from 0 to length(value[-1])-length(replace(value[-1],',')) increment 1]
 10           = regexp_substr(value[-1],'[^,]+',1,cv(i)+1)
 11         )
 12   order by id
 13       , i
 14  /

        ID VALUE
---------- -------------------
        10 A
        10 B
        10 C
        11 A
        11 B
        12 A
        12 B
        12 C
        12 D
        12 E
        12 F
        13 R
        13 T
        13 D
        13 W
        13 W
        13 W
        13 W
        13 W
        13 S
        13 S

21 rows selected.

在此博客文章中，我为此类查询编写了多达6种替代方法: http://rwijk.blogspot.com/2007/11/interval-based-row-generation.html

I have written up to 6 alternatives for this type of query in this blogpost: http://rwijk.blogspot.com/2007/11/interval-based-row-generation.html

关于， 罗布.

这篇关于PL/SQL中的表转换/字段解析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！