如何实现行转列(横表转成纵表)

使用 case ...
when ..then ..
when.. then ..
...
end

TRY IT ..
CREATE TABLE TEST_ABC (A NUMBER,B VARCHAR2(10));INSERT INTO TEST_ABC VALUES(1,'a+b+c');
INSERT INTO TEST_ABC VALUES(1,'e+f+g');
INSERT INTO TEST_ABC VALUES(3,'h+i+j+k+l');COMMIT;
SQL> SELECT * FROM TEST_ABC;         A B
---------- ----------
         1 a+b+c
         2 e+f+g
         3 h+i+j+k+l-- 1ST WAY:
SQL> SELECT A,
  2         SUBSTR('+' || B || '+',
  3                INSTR('+' || B || '+', '+', 1, RN) + 1,
  4                INSTR('+' || B || '+', '+', 1, RN + 1) -
  5                INSTR('+' || B || '+', '+', 1, RN) - 1) "NEW_B"
  6    FROM TEST_ABC,
  7         (SELECT ROWNUM RN
  8            FROM DUAL
  9          CONNECT BY ROWNUM <=
10                     (SELECT MAX(LENGTH(B) - LENGTH(REPLACE(B, '+', '')))
11                        FROM TEST_ABC)) OB
12   WHERE INSTR(B || '+', '+', 1, RN) > 0;         A NEW_B
---------- ------------------------
         1 a
         2 e
         3 h
         1 b
         2 f
         3 i
         1 c
         2 g
         3 j
         3 k
         3 l11 rows selected-- 2ND WAY:
SQL> SELECT A,
  2         SUBSTR('+' || B || '+',
  3                INSTR('+' || B || '+', '+', 1, RN) + 1,
  4                INSTR('+' || B || '+', '+', 1, RN + 1) -
  5                INSTR('+' || B || '+', '+', 1, RN) - 1) "NEW_B"
  6    FROM TEST_ABC,
  7         (SELECT ROWNUM RN
  8            FROM ALL_OBJECTS
  9           WHERE ROWNUM <= (SELECT MAX(LENGTH(B) - LENGTH(REPLACE(B, '+', '')))
10                              FROM TEST_ABC) + 1) OB
11   WHERE INSTR(B || '+', '+', 1, RN) > 0;         A NEW_B
---------- ------------------------
         1 a
         1 b
         1 c
         2 e
         2 f
         2 g
         3 h
         3 i
         3 j
         3 k
         3 l11 rows selectedSQL>

通用方法：
DROP TABLE test;
CREATE TABLE TEST  (x NUMBER,y VARCHAR2(10));
INSERT INTO TEST  VALUES(1,'a+b+c');
INSERT INTO TEST VALUES(2,'e+f+g');
--注意取要分几次的时候需要根据'+'的数量加1,如果不加1会丢最后一列
SELECT x,
       SUBSTR('+' || y || '+',
              INSTR('+' || y || '+', '+', 1, XX.RN) + 1,
              INSTR('+' || y || '+', '+', 1, XX.RN + 1) -
              INSTR('+' || y || '+', '+', 1, XX.RN) - 1) y
  FROM TEST,
       (SELECT ROWNUM RN
          FROM DUAL
        CONNECT BY ROWNUM <=
                   (SELECT MAX(LENGTH(y) - LENGTH(REPLACE(y, '+')) + 1)
                      FROM TEST)) XX
WHERE INSTR('+' || y, '+', 1, XX.RN) > 0
ORDER BY x;
--输出:
X Y
1 a
1 b
1 c
2 f
2 g
2 e

8i,9i,10G用：SQL> select * from t_str_col;                                     ID C123
--------------------------------------- --------------------------------
                                      1 v11,v21,v31
                                      2 v12,v22,
                                      3 v13,,v33
                                      4 ,v24,v34
                                      5 v15,,
                                      6 ,,v35
                                      7 ,,7 rows selectedSQL>
SQL> SELECT id,
  2  c123,
  3  substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,
  4  substr(c123,
  5  instr(c123 || ',', ',', 1, 1) + 1,
  6  instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,
  7  substr(c123,
  8  instr(c123 || ',', ',', 1, 2) + 1,
  9  instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3
10  FROM t_str_col
11  ORDER BY 1;                                     ID C123                             C1                                                                               C2                                                                               C3
--------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
                                      1 v11,v21,v31                      v11                                                                              v21                                                                              v31
                                      2 v12,v22,                         v12                                                                              v22
                                      3 v13,,v33                         v13                                                                                                                                                               v33
                                      4 ,v24,v34                                                                                                          v24                                                                              v34
                                      5 v15,,                            v15
                                      6 ,,v35                                                                                                                                                                                              v35
                                      7 ,,                                                                                                                                                                                                 7 rows selectedSQL>
10g也可以用：
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;

使用decodeselect decode(col,'a',1,'b',1,'c',1,'e',2,'f',2,'g',2,col),col from tname;

简单明了。
前面用connect by 的你们工作的时候也这样写的么？好麻烦啊

给个参考：
http://lengyue.javaeye.com/blog/195441

select id, substr(name, id, 1) as name
from (
select row_number() over(order by a.id) as rn,
       a.id,
       a.name
from a, (select rownum from dual connect by rownum < 4) b
     ) x

调试易

如何实现行转列(横表转成纵表)

解决方案 »