Let us assume that we have a result set (select or subselect) with a column which contains values we want concatenate using some sort of separators.
For example we have the following data:
How can we concatenate these values within single SQL select using Oracle dialect to get string aa,bb,cc,… ? Of course this problem could be solved with help of PL/SQL within stored procedure, but sometimes it is unavailable option. Actually, I had never thought about it before I was asked for advice in ICQ this morning. Right away I realized that Oracle analytical/hierarchical or another specific technique could help here. As I’m not familiar with whole range of additional non-ANSI SQL constructions I forwarded this question to my good friend and ex co-worker Sergey Kolomiets who gave me an interesting solution quite easily. For the reason of making note about this solution I decided to put it here:
with t as (
select 'aa' txt from dual
select 'bb' from dual
select 'cc' from dual
select 'dd' from dual
select 'zz' from dual
select 'yy' from dual ),
t_sub as (select rownum rn, t.* from t) select t_sub.*,
SYS_CONNECT_BY_PATH(txt,',') from t_sub
where rn=(select count(*) from t)
start with rn=1 connect by prior rn + 1 = rn
Of course it might not be the best high performance select, but main idea of using SYS_CONNECT_BY_PATH function is clear. In case you want to get rid of heading comma function [code lang="sql"]ltrim(...,',')[/code] can be used.
Before posting I’ve managed some google investigation and found nice
article dedicated to this issue: String Aggregation Techniques .