主页 / dba / 问题 / 43469
Accepted
Cade Roux
Asked: 2013-05-31 11:31:59 +0800 CST

Oracle 在具有分隔数据的列上进行透视

  • 772

我的数据是这样的：

keycol,col1,col2,col3
1,a;b;c,some data,some other data
2,x,some data,some other data
3,y;z,some data,some other data

在源系统中用分号分隔列的位置。

我想将其转向：

1,a,some data,some other data
1,b,some data,some other data
1,c,some data,some other data
2,x,some data,some other data
3,y,some data,some other data
3,z,some data,some other data

我在这里找到了一种技术，但我不能完全让它发挥作用：

CREATE TABLE yt
    (keycol int, col1 varchar2(5), col2 varchar2(9), col3 varchar2(15))
;

INSERT ALL 
    INTO yt (keycol, col1, col2, col3)
         VALUES (1, 'a;b;c', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL 
    INTO yt (keycol, col1, col2, col3)
         VALUES (2, 'x', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL 
    INTO yt (keycol, col1, col2, col3)
         VALUES (3, 'y;z', 'some data', 'some other data')
SELECT * FROM dual
;

我想我可以通过这种方式在 CONNECT BY 中包含 keycol 以获得并行递归链，但我想它不会那样工作。我很确定我已经在 SQL Server 中使用递归 CTE 完成了此操作。

SELECT keycol
  ,trim(regexp_substr(col1, '[^;]+', 1, level)) col1
  ,col2 
  ,col3
FROM yt t
CONNECT BY keycol = PRIOR keycol AND instr(col1, ';', 1, level - 1) > 0

http://sqlfiddle.com/#!4/3d378

FWIW，我在 Oracle 10g 上。

oracle oracle-10g

1 个回答

  1. Best Answer

    使用此链接中的代码作为起点，这应该会为您提供结果而不需要DISTINCT：

    select t.keycol,
    regexp_substr ( t.col1, '[^;]+', 1, n) as col1,
    t.col2,
    t.col3
from yt t 
cross join
(
    select level n
    from 
    (
        select max(length(col1) - length(replace(col1, ';'))) as max_semi
        from yt
    )
    connect by level <= 1 + max_semi
) c
where c.n <= 1 + length(col1) - length(replace(col1, ';'))
order by t.keycol, col1;

    请参阅带有演示的 SQL Fiddle

    • 3

相关问题