Need query to display single row to multiple row
ex:
A,B,C,D,E,f
change to
A
B
C
D
E
F
query to display multiple row to single row
ex:
A
B
C
D
E
F
change to
A,B,C,D,E,F
2
-
1Traditionally that is a presentation layer issue, and having comma separated values in a database is an anti-pattern/code-smell. Relational-Database design patterns are for normalised data, not comma separated lists. Do you have a use-case that shows why you need to de-nomralise your data structures? Perhaps we can point you at a different pattern that does not require that.MatBailie– MatBailie2014-03-06 18:16:32 +00:00Commented Mar 6, 2014 at 18:16
-
try the wm_concat functionjle– jle2014-03-06 18:17:09 +00:00Commented Mar 6, 2014 at 18:17
Add a comment
|
2 Answers
SQL> with t as (
2 select 'A,B,C,D,E,F' x from dual
3 )
4 select substr(x,(rownum-1)*2+1,1) from t
5 connect by level <= regexp_count(x,',')+1
6 /
S
-
A
B
C
D
E
F
SQL> with t as (
2 select 'A,B,C,D,E,F' x from dual
3 )
4 select listagg(s,',') within group (order by s) str
5 from (
6 select substr(x,(rownum-1)*2+1,1) s from t
7 connect by level <= regexp_count(x,',')+1
8 )
9 /
STR
---------------
A,B,C,D,E,F
Comments
Comma/string to table: How can I select from list of values in Oracle
select column_value from
table(sys.dbms_debug_vc2coll('One', 'Two', 'Three', 'Four'));
SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
FROM dual
CONNECT BY LEVEL <= regexp_count('Err1, Err2, Err3', '[^,]+')
/
Table to comma/string:
WM_CONCAT() in 10g as jle mentioned or LISTAGG() in 11g.
