字段分拆显示

Question

原始数据
   date                   类型      故障id
2011-08-09 12:34:10 6 402
2011-08-09 12:49:21 6 823,832
2011-08-09 12:51:12 6 479,499
2011-08-09 12:59:21 5 807,805
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402,801
2011-08-09 13:37:27 6 826,832我想得到最后的内容为
2011-08-09 12:34:10 6 402
2011-08-09 12:49:21 6 823
2011-08-09 12:49:21 6 832
2011-08-09 12:51:12 6 479
2011-08-09 12:51:12 6 499
2011-08-09 12:59:21 5 807
2011-08-09 12:59:21 5 805
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402
2011-08-09 13:33:23 7 801
2011-08-09 13:37:27 6 826
2011-08-09 13:37:27 6 832
这样sql 要怎么写呢？

Answer 1

这种问题怎么老是重复发呢 
用charindex+master..spt_values啊 

Answer 2

[SYS@myorcl] SQL>WITH T1 AS(
  2  SELECT TO_DATE('2011-08-09 12:34:10','YYYY-MM-DD HH24:MI:SS') A,'6' B, '402'     C FROM DUAL UNION ALL
  3  SELECT TO_DATE('2011-08-09 12:49:21','YYYY-MM-DD HH24:MI:SS') A,'6' B, '823,832' C FROM DUAL UNION ALL
  4  SELECT TO_DATE('2011-08-09 12:51:12','YYYY-MM-DD HH24:MI:SS') A,'6' B, '479,499' C FROM DUAL UNION ALL
  5  SELECT TO_DATE('2011-08-09 12:59:21','YYYY-MM-DD HH24:MI:SS') A,'5' B, '807,805' C FROM DUAL UNION ALL
  6  SELECT TO_DATE('2011-08-09 13:23:16','YYYY-MM-DD HH24:MI:SS') A,'6' B, '832'     C FROM DUAL UNION ALL
  7  SELECT TO_DATE('2011-08-09 13:33:23','YYYY-MM-DD HH24:MI:SS') A,'7' B, '402,801' C FROM DUAL UNION ALL
  8  SELECT TO_DATE('2011-08-09 13:37:27','YYYY-MM-DD HH24:MI:SS') A,'6' B, '826,832' C FROM DUAL
  9  )SELECT A, B, COLUMN_VALUE
 10    FROM T1,
 11         TABLE(CAST(MULTISET
 12                    (SELECT SUBSTR(C,
 13                                   DECODE(LEVEL,
 14                                          1,
 15                                          1,
 16                                          DECODE(INSTR(C, ',', 1, LEVEL - 1),
 17                                                 0,
 18                                                 LENGTH(C),
 19                                                 INSTR(C, ',', 1, LEVEL - 1) + 1)),
 20                                   DECODE(INSTR(C, ',', 1, LEVEL),
 21                                          0,
 22                                          LENGTH(C),
 23                                          INSTR(C, ',', 1, LEVEL) - 1))
 24                       FROM DUAL
 25                     CONNECT BY LEVEL <=
 26                                LENGTH(C) - LENGTH(REPLACE(C, ',')) + 1) AS
 27                    SYS.ODCIVARCHAR2LIST));A                   B COLUMN_VALUE
------------------- - ---------------
2011-08-09 12:34:10 6 402
2011-08-09 12:49:21 6 823
2011-08-09 12:49:21 6 832
2011-08-09 12:51:12 6 479
2011-08-09 12:51:12 6 499
2011-08-09 12:59:21 5 807
2011-08-09 12:59:21 5 805
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402
2011-08-09 13:33:23 7 801
2011-08-09 13:37:27 6 826
2011-08-09 13:37:27 6 832已选择12行。[SYS@myorcl] SQL>

Answer 3

--有点小bug，超过1个逗号有问题，改了改
[SYS@myorcl] SQL>WITH T1 AS(
  2    SELECT TO_DATE('2011-08-09 12:34:10','YYYY-MM-DD HH24:MI:SS') A,'6' B, '402,832'     C FROM DUAL UNION ALL
  3    SELECT TO_DATE('2011-08-09 12:49:21','YYYY-MM-DD HH24:MI:SS') A,'6' B, '823,832,aaa,234,546,sdffg' C FROM DUAL UNION ALL
  4    SELECT TO_DATE('2011-08-09 12:51:12','YYYY-MM-DD HH24:MI:SS') A,'6' B, '479,499,832' C FROM DUAL UNION ALL
  5    SELECT TO_DATE('2011-08-09 12:59:21','YYYY-MM-DD HH24:MI:SS') A,'5' B, '807,805,832' C FROM DUAL UNION ALL
  6    SELECT TO_DATE('2011-08-09 13:23:16','YYYY-MM-DD HH24:MI:SS') A,'6' B, '832,832'     C FROM DUAL UNION ALL
  7    SELECT TO_DATE('2011-08-09 13:33:23','YYYY-MM-DD HH24:MI:SS') A,'7' B, '402,801,832' C FROM DUAL UNION ALL
  8    SELECT TO_DATE('2011-08-09 13:37:27','YYYY-MM-DD HH24:MI:SS') A,'6' B, '826,832,832' C FROM DUAL
  9    )SELECT A, B, COLUMN_VALUE
 10      FROM T1,
 11           TABLE(CAST(MULTISET
 12                      (SELECT SUBSTR(C,
 13                                     DECODE(LEVEL,
 14                                            1,
 15                                            1,
 16                                            DECODE(INSTR(C, ',', 1, LEVEL - 1),
 17                                                   0,
 18                                                   LENGTH(C),
 19                                                   INSTR(C, ',', 1, LEVEL - 1) + 1)),
 20                                     DECODE(INSTR(C, ',', 1, LEVEL),
 21                                            0,
 22                                            LENGTH(C),
 23                                            INSTR(C, ',', 1, LEVEL) - 1)
 24                                     -
 25                                     DECODE(LEVEL,
 26                                            1,
 27                                            1,
 28                                            DECODE(INSTR(C, ',', 1, LEVEL - 1),
 29                                                   0,
 30                                                   LENGTH(C),
 31                                                   INSTR(C, ',', 1, LEVEL - 1) + 1))+1)
 32                         FROM DUAL
 33                       CONNECT BY LEVEL <=
 34                                  LENGTH(C) - LENGTH(REPLACE(C, ',')) + 1) AS
 35                      SYS.ODCIVARCHAR2LIST));A                   B COLUMN_VALUE
------------------- - --------------------
2011-08-09 12:34:10 6 402
2011-08-09 12:34:10 6 832
2011-08-09 12:49:21 6 823
2011-08-09 12:49:21 6 832
2011-08-09 12:49:21 6 aaa
2011-08-09 12:49:21 6 234
2011-08-09 12:49:21 6 546
2011-08-09 12:49:21 6 sdffg
2011-08-09 12:51:12 6 479
2011-08-09 12:51:12 6 499
2011-08-09 12:51:12 6 832
2011-08-09 12:59:21 5 807
2011-08-09 12:59:21 5 805
2011-08-09 12:59:21 5 832
2011-08-09 13:23:16 6 832
2011-08-09 13:23:16 6 832
2011-08-09 13:33:23 7 402
2011-08-09 13:33:23 7 801
2011-08-09 13:33:23 7 832
2011-08-09 13:37:27 6 826
2011-08-09 13:37:27 6 832
2011-08-09 13:37:27 6 832已选择22行。[SYS@myorcl] SQL>

Answer 4

--再写一个
[SYS@orcl] SQL>WITH T1 AS(
  2    SELECT TO_DATE('2011-08-09 12:34:10','YYYY-MM-DD HH24:MI:SS') A,'6' B, '402,832'     C FROM DUAL UNION ALL
  3    SELECT TO_DATE('2011-08-09 12:49:21','YYYY-MM-DD HH24:MI:SS') A,'6' B, '823,832,aaa,234,546,sdffg' C FROM DUAL UNION ALL
  4    SELECT TO_DATE('2011-08-09 12:51:12','YYYY-MM-DD HH24:MI:SS') A,'6' B, '479,499,832' C FROM DUAL UNION ALL
  5    SELECT TO_DATE('2011-08-09 12:59:21','YYYY-MM-DD HH24:MI:SS') A,'5' B, '807,805,832' C FROM DUAL UNION ALL
  6    SELECT TO_DATE('2011-08-09 13:23:16','YYYY-MM-DD HH24:MI:SS') A,'6' B, '832,832'     C FROM DUAL UNION ALL
  7    SELECT TO_DATE('2011-08-09 13:33:23','YYYY-MM-DD HH24:MI:SS') A,'7' B, '402,801,832' C FROM DUAL UNION ALL
  8    SELECT TO_DATE('2011-08-09 13:37:27','YYYY-MM-DD HH24:MI:SS') A,'6' B, '826,832,832' C FROM DUAL
  9  )SELECT A, B, C, REGEXP_SUBSTR(C,'[^, ]+',1,L)
 10     FROM T1,(SELECT LEVEL L FROM DUAL CONNECT BY LEVEL<=100) T2
 11    WHERE LENGTH(T1.C)-LENGTH(REPLACE(T1.C,','))+1>=T2.L
 12    ORDER BY C;A          B C                         REGEXP_SUBSTR(C,'[^,]+',1,L)
---------- - ------------------------- --------------------------------------------------
2011-08-09 7 402,801,832               801
2011-08-09 7 402,801,832               402
2011-08-09 7 402,801,832               832
2011-08-09 6 402,832                   832
2011-08-09 6 402,832                   402
2011-08-09 6 479,499,832               499
2011-08-09 6 479,499,832               479
2011-08-09 6 479,499,832               832
2011-08-09 5 807,805,832               807
2011-08-09 5 807,805,832               805
2011-08-09 5 807,805,832               832
2011-08-09 6 823,832,aaa,234,546,sdffg 832
2011-08-09 6 823,832,aaa,234,546,sdffg sdffg
2011-08-09 6 823,832,aaa,234,546,sdffg 823
2011-08-09 6 823,832,aaa,234,546,sdffg 546
2011-08-09 6 823,832,aaa,234,546,sdffg 234
2011-08-09 6 823,832,aaa,234,546,sdffg aaa
2011-08-09 6 826,832,832               826
2011-08-09 6 826,832,832               832
2011-08-09 6 826,832,832               832
2011-08-09 6 832,832                   832
2011-08-09 6 832,832                   832已选择22行。[SYS@orcl] SQL>