`
easy0917
  • 浏览: 261091 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
社区版块
存档分类
最新评论

行列转换（转）

 
阅读更多 
CREATE TABLE t_col_row(
ID INT,
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10));

INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');
INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);
INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');
INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');
INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);
INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35');
INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);
COMMIT;
SELECT * FROM t_col_row;

1)UNION ALL
适用范围：8i,9i,10g及以后版本 
SELECT ID, 'c1' CN, C1 CV
  FROM T_COL_ROW
UNION ALL
SELECT ID, 'c2' CN, C2 CV
  FROM T_COL_ROW
UNION ALL
SELECT ID, 'c3' CN, C3 CV 
  FROM T_COL_ROW;

这里只是把C1和C2和C3的值放到第列展示
2)MODEL
适用范围：10g及以后 
SELECT id, cn, cv FROM t_col_row
MODEL
RETURN UPDATED ROWS
PARTITION BY (ID)
DIMENSION BY (0 AS n)
MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3)
RULES UPSERT ALL
(
  cn[1] = 'c1',
  cn[2] = 'c2',
  cn[3] = 'c3',
  cv[1] = c1[0],
  cv[2] = c2[0],
  cv[3] = c3[0]
  )
ORDER BY ID,cn;

3)collection
适用范围：8i,9i,10g及以后版本
要创建一个对象和一个集合： 
CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));

CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair;

SELECT id, t.cn AS cn, t.cv AS cv
  FROM t_col_row,
       TABLE(cv_varr(cv_pair('c1', t_col_row.c1),
                     cv_pair('c2', t_col_row.c2),
                     cv_pair('c3', t_col_row.c3))) t
ORDER BY 1, 2;

2、行转列 
CREATE TABLE t_row_col AS
SELECT id, 'c1' cn, c1 cv
  FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
  FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;

SELECT * FROM t_row_col ORDER BY 1,2;

1)AGGREGATE FUNCTION
适用范围：8i,9i,10g及以后版本 

SELECT id,
       MAX(decode(cn, 'c1', cv, NULL)) AS c1,
       MAX(decode(cn, 'c2', cv, NULL)) AS c2,
       MAX(decode(cn, 'c3', cv, NULL)) AS c3
  FROM t_row_col
GROUP BY id
ORDER BY 1;

MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。

被指定的转置列只能有一列，但固定的列可以有多列，请看下面的例子： 
SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;

SELECT mgr,
       deptno,
       MAX(decode(empno, '7788', ename, NULL)) "7788",
       MAX(decode(empno, '7902', ename, NULL)) "7902",
       MAX(decode(empno, '7844', ename, NULL)) "7844",
       MAX(decode(empno, '7521', ename, NULL)) "7521",
       MAX(decode(empno, '7900', ename, NULL)) "7900",
       MAX(decode(empno, '7499', ename, NULL)) "7499",
       MAX(decode(empno, '7654', ename, NULL)) "7654"
  FROM emp
WHERE mgr IN (7566, 7698)
   AND deptno IN (20, 30)
GROUP BY mgr, deptno
ORDER BY 1, 2;


这里转置列为empno，固定列为mgr，deptno。

还有一种行转列的方式，就是相同组中的行值变为单个列值，但转置的行值不变为列名：

--------------------------------------------------------------------------------------------------------

ID        CN_1        CV_1        CN_2        CV_2        CN_3        CV_3
1          c1             v11           c2             v21           c3             v31
2          c1             v12           c2             v22           c3       
3          c1             v13           c2                              c3             v33
4          c1                               c2             v24          c3             v34
5          c1             v15           c2                              c3               
6          c1                              c2                               c3             v35
7          c1                              c2                               c3
这种情况可以用分析函数实现： 

SELECT id,
       MAX(decode(rn, 1, cn, NULL)) cn_1,
       MAX(decode(rn, 1, cv, NULL)) cv_1,
       MAX(decode(rn, 2, cn, NULL)) cn_2,
       MAX(decode(rn, 2, cv, NULL)) cv_2,
       MAX(decode(rn, 3, cn, NULL)) cn_3,
       MAX(decode(rn, 3, cv, NULL)) cv_3
  FROM (SELECT id,
               cn,
               cv,
               row_number() over(PARTITION BY id ORDER BY cn, cv) rn
          FROM t_row_col)
GROUP BY ID;


2)PL/SQL
适用范围：8i,9i,10g及以后版本
这种对于行值不固定的情况可以使用。
下面是我写的一个包，包中
p_rows_column_real用于前述的第一种不限定列的转换；
p_rows_column用于前述的第二种不限定列的转换。 

CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS
  TYPE refc IS REF CURSOR;

  PROCEDURE p_print_sql(p_txt VARCHAR2);

  FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
    RETURN VARCHAR2;

  PROCEDURE p_rows_column(p_table      IN VARCHAR2,
                          p_keep_cols  IN VARCHAR2,
                          p_pivot_cols IN VARCHAR2,
                          p_where      IN VARCHAR2 DEFAULT NULL,
                          p_refc       IN OUT refc);

  PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,
                               p_keep_cols IN VARCHAR2,
                               p_pivot_col IN VARCHAR2,
                               p_pivot_val IN VARCHAR2,
                               p_where     IN VARCHAR2 DEFAULT NULL,
                               p_refc      IN OUT refc);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS

  PROCEDURE p_print_sql(p_txt VARCHAR2) IS
    v_len INT;
  BEGIN
    v_len := length(p_txt);
    FOR i IN 1 .. v_len / 250 + 1 LOOP
      dbms_output.put_line(substrb(p_txt, (i - 1) * 250 + 1, 250));
    END LOOP;
  END;

  FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
    RETURN VARCHAR2 IS
    v_first INT;
    v_last  INT;
  BEGIN
    IF p_seq < 1 THEN
      RETURN NULL;
    END IF;
    IF p_seq = 1 THEN
      IF instr(p_str, p_division, 1, p_seq) = 0 THEN
        RETURN p_str;
      ELSE
        RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);
      END IF;
    ELSE
      v_first := instr(p_str, p_division, 1, p_seq - 1);
      v_last  := instr(p_str, p_division, 1, p_seq);
      IF (v_last = 0) THEN
        IF (v_first > 0) THEN
          RETURN substr(p_str, v_first + 1);
        ELSE
          RETURN NULL;
        END IF;
      ELSE
        RETURN substr(p_str, v_first + 1, v_last - v_first - 1);
      END IF;
    END IF;
  END f_split_str;

  PROCEDURE p_rows_column(p_table      IN VARCHAR2,
                          p_keep_cols  IN VARCHAR2,
                          p_pivot_cols IN VARCHAR2,
                          p_where      IN VARCHAR2 DEFAULT NULL,
                          p_refc       IN OUT refc) IS
    v_sql VARCHAR2(4000);
    TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    v_keep v_keep_ind_by;
  
    TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    v_pivot v_pivot_ind_by;
  
    v_keep_cnt   INT;
    v_pivot_cnt  INT;
    v_max_cols   INT;
    v_partition  VARCHAR2(4000);
    v_partition1 VARCHAR2(4000);
    v_partition2 VARCHAR2(4000);
  BEGIN
    v_keep_cnt  := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
    v_pivot_cnt := length(p_pivot_cols) -
                   length(REPLACE(p_pivot_cols, ',')) + 1;
    FOR i IN 1 .. v_keep_cnt LOOP
      v_keep(i) := f_split_str(p_keep_cols, ',', i);
    END LOOP;
    FOR j IN 1 .. v_pivot_cnt LOOP
      v_pivot(j) := f_split_str(p_pivot_cols, ',', j);
    END LOOP;
    v_sql := 'select max(count(*)) from ' || p_table || ' group by ';
    FOR i IN 1 .. v_keep.LAST LOOP
      v_sql := v_sql || v_keep(i) || ',';
    END LOOP;
    v_sql := rtrim(v_sql, ',');
    EXECUTE IMMEDIATE v_sql
      INTO v_max_cols;
    v_partition := 'select ';
    FOR x IN 1 .. v_keep.COUNT LOOP
      v_partition1 := v_partition1 || v_keep(x) || ',';
    END LOOP;
    FOR y IN 1 .. v_pivot.COUNT LOOP
      v_partition2 := v_partition2 || v_pivot(y) || ',';
    END LOOP;
    v_partition1 := rtrim(v_partition1, ',');
    v_partition2 := rtrim(v_partition2, ',');
    v_partition  := v_partition || v_partition1 || ',' || v_partition2 ||
                    ', row_number() over (partition by ' || v_partition1 ||
                    ' order by ' || v_partition2 || ') rn from ' || p_table;
    v_partition  := rtrim(v_partition, ',');
    v_sql        := 'select ';
    FOR i IN 1 .. v_keep.COUNT LOOP
      v_sql := v_sql || v_keep(i) || ',';
    END LOOP;
    FOR i IN 1 .. v_max_cols LOOP
      FOR j IN 1 .. v_pivot.COUNT LOOP
        v_sql := v_sql || ' max(decode(rn,' || i || ',' || v_pivot(j) ||
                 ',null))' || v_pivot(j) || '_' || i || ',';
      END LOOP;
    END LOOP;
    IF p_where IS NOT NULL THEN
      v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' ||
               p_where || ') group by ';
    ELSE
      v_sql := rtrim(v_sql, ',') || ' from (' || v_partition ||
               ') group by ';
    END IF;
    FOR i IN 1 .. v_keep.COUNT LOOP
      v_sql := v_sql || v_keep(i) || ',';
    END LOOP;
    v_sql := rtrim(v_sql, ',');
    p_print_sql(v_sql);
    OPEN p_refc FOR v_sql;
  EXCEPTION
    WHEN OTHERS THEN
      OPEN p_refc FOR
        SELECT 'x' FROM dual WHERE 0 = 1;
  END;

  PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,
                               p_keep_cols IN VARCHAR2,
                               p_pivot_col IN VARCHAR2,
                               p_pivot_val IN VARCHAR2,
                               p_where     IN VARCHAR2 DEFAULT NULL,
                               p_refc      IN OUT refc) IS
    v_sql VARCHAR2(4000);
    TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    v_keep v_keep_ind_by;
    TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    v_pivot    v_pivot_ind_by;
    v_keep_cnt INT;
    v_group_by VARCHAR2(2000);
  BEGIN
    v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
    FOR i IN 1 .. v_keep_cnt LOOP
      v_keep(i) := f_split_str(p_keep_cols, ',', i);
    END LOOP;
    v_sql := 'select ' || 'cast(' || p_pivot_col ||
             ' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table ||
             ' group by ' || p_pivot_col;
    EXECUTE IMMEDIATE v_sql BULK COLLECT
      INTO v_pivot;
    FOR i IN 1 .. v_keep.COUNT LOOP
      v_group_by := v_group_by || v_keep(i) || ',';
    END LOOP;
    v_group_by := rtrim(v_group_by, ',');
    v_sql      := 'select ' || v_group_by || ',';
  
    FOR x IN 1 .. v_pivot.COUNT LOOP
      v_sql := v_sql || ' max(decode(' || p_pivot_col || ',' || chr(39) ||
               v_pivot(x) || chr(39) || ',' || p_pivot_val ||
               ',null)) as "' || v_pivot(x) || '",';
    END LOOP;
    v_sql := rtrim(v_sql, ',');
    IF p_where IS NOT NULL THEN
      v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' ||
               v_group_by;
    ELSE
      v_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_by;
    END IF;
    p_print_sql(v_sql);
    OPEN p_refc FOR v_sql;
  EXCEPTION
    WHEN OTHERS THEN
      OPEN p_refc FOR
        SELECT 'x' FROM dual WHERE 0 = 1;
  END;

END;
/

3.多列转换成字符串
CREATE TABLE t_col_str AS
SELECT * FROM t_col_row;
这个比较简单，用||或concat函数可以实现：
SELECT concat('a','b') FROM dual;



1)|| OR concat
适用范围：8i,9i,10g及以后版本 
SELECT * FROM t_col_str;
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_str;

4.多行转换成字符串 

CREATE TABLE t_row_str(
ID INT,
col VARCHAR2(10));

INSERT INTO t_row_str VALUES(1,'a');
INSERT INTO t_row_str VALUES(1,'b');
INSERT INTO t_row_str VALUES(1,'c');
INSERT INTO t_row_str VALUES(2,'a');
INSERT INTO t_row_str VALUES(2,'d');
INSERT INTO t_row_str VALUES(2,'e');
INSERT INTO t_row_str VALUES(3,'c');
COMMIT;

SELECT * FROM t_row_str;


1)MAX + decode
适用范围：8i,9i,10g及以后版本 
SELECT id,
       MAX(decode(rn, 1, col, NULL)) ||
       MAX(decode(rn, 2, ',' || col, NULL)) ||
       MAX(decode(rn, 3, ',' || col, NULL)) str
  FROM (SELECT id,
               col,
               row_number() over(PARTITION BY id ORDER BY col) AS rn
          FROM t_row_str) t
GROUP BY id
ORDER BY 1;


2)row_number + lead
适用范围：8i,9i,10g及以后版本 
SELECT id, str
  FROM (SELECT id,
               row_number() over(PARTITION BY id ORDER BY col) AS rn,
               col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col) || 
               lead(',' || col, 2) over(PARTITION BY id ORDER BY col) || 
               lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS str
          FROM t_row_str)
WHERE rn = 1
ORDER BY 1;



3)MODEL
适用范围：10g及以后版本        
SELECT id, substr(str, 2) str FROM t_row_str
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
MEASURES (CAST(col AS VARCHAR2(20)) AS str)
RULES UPSERT 
ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
           (str[0] = str[0] || ',' || str[iteration_number+1])
ORDER BY 1;

4)sys_connect_by_path
适用范围：8i,9i,10g及以后版本 
SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
          FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
       AND id = PRIOR id
GROUP BY t.id;

适用范围：10g及以后版本

Sql代码  
SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
          FROM t_row_str) t
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
       AND id = PRIOR id;

5)wmsys.wm_concat
适用范围：10g及以后版本
这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。 
SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/')
  FROM t_row_str
GROUP BY id;


5.字符串转换成多列
其实际上就是一个字符串拆分的问题。 
 
CREATE TABLE t_str_col AS
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_str;

SELECT * FROM t_str_col;

1)substr + instr
适用范围：8i,9i,10g及以后版本 
SELECT id,
       c123,
       substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,
       substr(c123,
              instr(c123 || ',', ',', 1, 1) + 1,
              instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,
       substr(c123,
              instr(c123 || ',', ',', 1, 2) + 1,
              instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3
  FROM t_str_col
ORDER BY 1;

2)regexp_substr
适用范围：10g及以后版本

Sql代码  
SELECT id,
       c123,
       rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 1), ',') AS c1,
       rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 2), ',') AS c2,
       rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 3), ',') AS c3
  FROM t_str_col
ORDER BY 1;

6.字符串转换成多行 
CREATE TABLE t_str_row AS
SELECT id,
       MAX(decode(rn, 1, col, NULL)) ||
       MAX(decode(rn, 2, ',' || col, NULL)) ||
       MAX(decode(rn, 3, ',' || col, NULL)) str
  FROM (SELECT id,
               col,
               row_number() over(PARTITION BY id ORDER BY col) AS rn
          FROM t_row_str) t
GROUP BY id
ORDER BY 1;

SELECT * FROM t_str_row;

1)UNION ALL
适用范围：8i,9i,10g及以后版本 
SELECT id, 1 AS p, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cv
  FROM t_str_row
UNION ALL
SELECT id,
       2 AS p,
       substr(str,
              instr(str || ',', ',', 1, 1) + 1,
              instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
  FROM t_str_row
UNION ALL
SELECT id,
       3 AS p,
       substr(str,
              instr(str || ',', ',', 1, 1) + 1,
              instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
  FROM t_str_row
ORDER BY 1, 2;

适用范围：10g及以后版本 

SELECT id, 1 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 1), ',') AS cv
  FROM t_str_row
UNION ALL
SELECT id, 2 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 2), ',') AS cv
  FROM t_str_row
UNION ALL
SELECT id, 3 AS p, rtrim(regexp_substr(str||',', '.*?' || ',',1,3), ',') AS cv
  FROM t_str_row
ORDER BY 1, 2;

2)VARRAY
适用范围：8i,9i,10g及以后版本
要创建一个可变数组： 
CREATE OR REPLACE TYPE ins_seq_type IS VARRAY(8) OF NUMBER;

SELECT * FROM TABLE(ins_seq_type(1, 2, 3, 4, 5));

SELECT t.id,
       c.column_value AS p,
       substr(t.ca,
              instr(t.ca, ',', 1, c.column_value) + 1,
              instr(t.ca, ',', 1, c.column_value + 1) -
              (instr(t.ca, ',', 1, c.column_value) + 1)) AS cv
  FROM (SELECT id,
               ',' || str || ',' AS ca,
               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
          FROM t_str_row) t
INNER JOIN TABLE(ins_seq_type(1, 2, 3)) c ON c.column_value <=
                                                     t.cnt
ORDER BY 1, 2;

3)SEQUENCE series
这类方法主要是要产生一个连续的整数列，产生连续整数列的方法有很多，主要有：
CONNECT BY,ROWNUM+all_objects,CUBE等。
适用范围：8i,9i,10g及以后版本 

SELECT t.id,
       c.lv AS p,
       substr(t.ca,
              instr(t.ca, ',', 1, c.lv) + 1,
              instr(t.ca, ',', 1, c.lv + 1) -
              (instr(t.ca, ',', 1, c.lv) + 1)) AS cv
  FROM (SELECT id,
               ',' || str || ',' AS ca,
               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
          FROM t_str_row) t,
       (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c
WHERE c.lv <= t.cnt
ORDER BY 1, 2;

SELECT t.id,
       c.rn AS p,
       substr(t.ca,
              instr(t.ca, ',', 1, c.rn) + 1,
              instr(t.ca, ',', 1, c.rn + 1) -
              (instr(t.ca, ',', 1, c.rn) + 1)) AS cv
  FROM (SELECT id,
               ',' || str || ',' AS ca,
               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
          FROM t_str_row) t,
       (SELECT rownum rn FROM all_objects WHERE rownum <= 5) c
WHERE c.rn <= t.cnt
ORDER BY 1, 2;

SELECT t.id,
       c.cb AS p,
       substr(t.ca,
              instr(t.ca, ',', 1, c.cb) + 1,
              instr(t.ca, ',', 1, c.cb + 1) -
              (instr(t.ca, ',', 1, c.cb) + 1)) AS cv
  FROM (SELECT id,
               ',' || str || ',' AS ca,
               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
          FROM t_str_row) t,
       (SELECT rownum cb FROM (SELECT 1 FROM dual GROUP BY CUBE(1, 2))) c
WHERE c.cb <= t.cnt
ORDER BY 1, 2;
适用范围：10g及以后版本 

SELECT t.id,
       c.lv AS p,
       rtrim(regexp_substr(t.str || ',', '.*?' || ',', 1, c.lv), ',') AS cv
  FROM (SELECT id,
               str,
               length(regexp_replace(str || ',', '[^' || ',' || ']', NULL)) AS cnt
          FROM t_str_row) t
INNER JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c ON c.lv <=
                                                                   t.cnt
ORDER BY 1, 2;
4)Hierarchical + DBMS_RANDOM
适用范围：10g及以后版本 
SELECT id,
       LEVEL AS p,
       rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv
  FROM t_str_row
CONNECT BY id = PRIOR id
       AND PRIOR dbms_random.VALUE IS NOT NULL
       AND LEVEL <=
           length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
ORDER BY 1, 2;

5)Hierarchical + CONNECT_BY_ROOT
适用范围：10g及以后版本 
SELECT id,
       LEVEL AS p,
       rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv
  FROM t_str_row
CONNECT BY id = connect_by_root id
       AND LEVEL <=
           length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
ORDER BY 1, 2;

6)MODEL
适用范围：10g及以后版本 
SELECT id, p, cv FROM t_str_row   
MODEL   
RETURN UPDATED ROWS  
PARTITION BY(ID)   
DIMENSION BY( 0 AS p)   
MEASURES( str||',' AS cv)   
RULES UPSERT   
(cv    
 [ FOR p   
        FROM 1 TO length(regexp_replace(cv[0],'[^'||','||']',null))   
        INCREMENT 1   
   ] = rtrim(regexp_substr( cv[0],'.*?'||',',1,cv(p)),','))   
ORDER BY 1,2;




分享到：
| 正则表达式参考
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    sql行列转换扩展

    在本文中，我们将介绍两种类型的行列转换扩展方法：行转列结构和行列转换自适应结构。 行转列结构 行转列结构是指将多个列合并为一列，行中有共同特征的数据。这种方法通常用于将多个课程或项目合并为一个单一的列...

    table行列数据转换

    在IT领域，尤其是在数据分析和处理时，经常需要对数据进行行列转换，以便更好地适应不同的应用场景。C#作为广泛使用的编程语言，提供了丰富的库和方法来处理这类问题。本篇文章将详细探讨“table动态行列数据转换”...

    Excel行列转换工具

    在Excel表格处理中，行列转换是一项非常常见的操作，尤其对于数据整理和分析至关重要。"Excel行列转换工具"正是为了简化这一过程而设计的，它提供了一键式的转换功能，使得用户能够快速、高效地在行与列之间切换，极...

    Sql语句实现表的行列转换，行转列，列转行

    ### SQL语句实现表的行列转换，行转列，列转行 在处理数据库时，我们经常需要对数据进行各种变换以适应不同的分析需求。其中，“行列转换”就是一种非常实用的功能，它可以帮助我们将表中的行数据转换为列数据，...

    Oracle行列转换

    在处理数据时，有时我们需要将数据从行格式转换为列格式，或者反之，这一过程被称为“行列转换”。Oracle提供了多种方法来实现这样的转换，这对于数据分析、报表制作以及优化查询性能等场景非常有用。下面我们将深入...

    行列转换的小工具 可以进行行列互转

    行列转换的小工具 可以进行行列互转，非常好用，

    oracle行列转换例子

    在Oracle数据库中，行列转换是一种常见的数据操作需求，主要用于将数据从行的形式转换为列的形式，或者反之。这种转换在数据分析、报表制作等场景中尤为常见。本文将深入解析一个Oracle行列转换的例子，通过详细解释...

    oracle行列转换总结

    在Oracle数据库中，行列转换是一项常用且强大的功能，它允许数据在不同的维度上进行转换，以便于数据分析和报告。本文将深入探讨Oracle中实现行列转换的几种方法，包括使用`UNION ALL`、`MODEL`子句以及集合类型(`...

    sql server 行列转换

    ### SQL Server 行列转换知识点解析 #### 一、行列转换概述 在处理数据库查询时，我们经常会遇到需要将表中的行数据转换为列数据的需求，这种操作通常被称为“行列转换”。例如，当我们需要汇总不同类别的数据并将...

    oracle sql 行列转换

    在Oracle SQL中，行列转换是一种常见的数据操作需求，主要用于将数据表中的行转换为列，或将列转换为行，以此来满足不同的数据展示或分析需求。这种转换在处理多维度数据、汇总数据或是进行复杂查询时特别有用。下面...

    SQL语句行列转换（附带数据库、表、视图操作）

    SQL 语句行列转换（附带数据库、表、视图操作） SQL 语句行列转换是数据库管理系统中的一种常见操作，它可以将数据从行转换为列，或者从列转换为行。在这个过程中，需要使用数据库管理语言（Database Management ...

    行列转，换行列转换

    ### 行列转换在SQL中的应用 #### 一、行列转换概述 行列转换是数据库查询中一项非常实用的功能，主要用于改变数据的展示形式，即将原始数据表中的行数据转换为列数据，或将列数据转换为行数据。这种转换在数据分析...

    oracle行列转换实例

    【Oracle 行列转换实例】 在数据库管理中，有时我们需要将数据表的行与列进行转换，以便于数据分析和报表展示。Oracle 提供了一种高效的方法，即使用分析函数来实现这种行列转换。分析函数主要设计用于处理累计计算...

    MSSQL行列转换存储过程

    其中，行列转换是数据处理中常见的一种需求，尤其是在进行数据分析、报表生成等场景时。本文将深入解析MSSQL中实现行列转换的存储过程及其工作原理，帮助读者理解和掌握这一关键技术。 ### MSSQL行列转换存储过程 ...

    行列转换

    在IT领域，行列转换是一种常见的数据处理操作，特别是在数据分析、数据库管理和编程中。这个操作涉及到将数据矩阵或表格从行格式转换为列格式，反之亦然。这种转换可以帮助我们更有效地处理和展示数据，尤其在统计...

    行列转换SQL存储过程代码

    本篇文章将深入探讨一个SQL存储过程的实现方式，该存储过程主要用于完成“行转列”（即行列转换）的操作。通过这种方式，可以有效地将数据库表中的行数据转换为列的形式进行展示或处理，这对于数据分析和报告生成等...

    mysql 查询行列转换

    本篇文章将深入探讨“MySQL 查询行列转换”的概念及其实际应用，这在数据分析和报表展示时尤其重要。 行转列是数据处理中的常见需求，尤其是在数据透视或汇总分析时。在 MySQL 中，我们可以使用几种方法实现这一...

    SQL 2008行列转换的pivot

    ### SQL 2008 行列转换 (Pivot) 的动态实现 #### 知识点一：行列转换（Pivot）的概念与应用场景 在数据库查询中，有时我们需要将数据表中的行转换为列，或者将列转换为行，这种操作被称为行列转换。行列转换在报表...

    一道java面试题 行列转换

    其中一种常见的转换需求是从行转列（即行列转换）。本篇文章将通过一个具体的Java面试题目，详细介绍如何在Oracle和SQL Server两种数据库环境下实现行列转换，并给出具体的示例代码。 #### 二、需求分析与设计 ...

Magicbox
Global site tag (gtag.js) - Google Analytics