IN MY OPINION

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:
[code lang="sql"]
with t as (
select 'aa' txt from dual
union all
select 'bb' from dual
union all
select 'cc' from dual
union all
select 'dd' from dual
union all
select 'zz' from dual
union all
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
[/code]

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 .

I’ve got a great opportunity to launch my blog once again. I’ve already successfully moved my weblog to the latest version of the WordPress blogging engine. Its updated feature list promises lots of useful things. One of them I liked the most is the ability to post a message by mail which becomes a new blog entry later. The blogging by sending e-mail feature is what I needed all this time. I’m not very familiar with all that on-line post forms because they all have lots of decorations and useless features that are always interrupting my train of thoughts. I’d rather prefer my old plain e-mail editor that allows me making drafts, sending them from my home to my work place and return to editing any time I need.

Despite the fact that this post appeared for testing purposes, I hope sincerely that from this time I start my blogging on more or less regular basis.

Why haven’t I done this before? I’m planning to dedicate a few next posts to this topic