SQL:列の分割


表の1つの列のレコードはカンマで区切られ、複数に分割されます.
例えば1 A,B,C,D
分割後に作成
1 A
2 B
3 C
4 D
SQL>
SQL>     WITH t AS
  2      (SELECT '2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsafe,adfe,feaf,aefae,dfe,sfe,afe]' col
  3        FROM   dual
  4        UNION ALL
  5        SELECT '2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsafe,adfe,feaf,aefae,dfe,sfe]'
  6        FROM   dual
  7        UNION ALL
  8        SELECT '2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsafe,adfe,feaf,aefae,dfe,sfe,afc]'
  9        FROM   dual)
 10      SELECT col,
 11             regexp_substr(col,
 12                           '[^][,]+',
 13                           1,
 14                           LEVEL + 1) AS ctx
 15      FROM   t
 16      CONNECT BY PRIOR dbms_random.value IS NOT NULL
 17                 AND LEVEL <= regexp_count(col,
 18                                           ',')
 19                 AND PRIOR col = col;
 
COL                                                                              CTX
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa test
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 23
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 3543
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4543.k
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4353
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sdfkj
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sadfsi
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa esf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dsfa
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa ewfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadfi
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fsafe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa feaf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa aefae
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa afe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa test
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 23
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 3543
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4543.k
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4353
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sdfkj
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sadfsi
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa esf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dsfa
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa ewfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadfi
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fsafe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa feaf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa aefae
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa test
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 23
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 3543
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4543.k
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4353
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sdfkj
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sadfsi
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa esf
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dsfa
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa ewfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadfi
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadf
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fsafe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa feaf
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa aefae
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa afc
 
59 rows selected