Concatenate String Issue

[TopJack]

In ANSI SQL is it possible to append a string field together over many records that are grouped ?

Example

a database list contains 10 records as follows :-

number letter
1 a
1 b
1 c
2 d
2 e
3 f
3 g
3 h
3 i
3 j

I want the SQL output to look like this :-

number string
1 a,b,c
2 d,e
3 f,g,h,i,j

I want to group the number field but create a concatenated string (comma delimited) showing all the sub records.

Is this possible?

 

[r937]

no, it isn't

r937.com | rudy.ca

 

[TopJack]

That was a quick straight answer. I was hoping that wasn't the answer though.

Thanks anyway.

 

[Fahtrim]

With 2 assumptions you can possibly. BUT this is best done with a 3GL language such as RPG, COBOL, JAVA etc.

Not best for SQL.

1. You know the maximum possible characters that can appear for a number. (ie 3 has 5 characters associated to it)

2. You don't have &/or don't care if there is more than one of the same character per number.

select a.el_count,
min(ifnull(a.el_char,'')||','||
ifnull(b.el_char,'')||','||
ifnull(c.el_char,'')||','||
ifnull(d.el_char,'')||','||
ifnull(e.el_char,''))
from bwtest a
left outer join bwtest b on b.el_count = a.el_count
and b.el_char <> a.el_char
left outer join bwtest c on c.el_count = a.el_count
and c.el_char <> b.el_char
and c.el_char <> a.el_char
left outer join bwtest d on d.el_count = a.el_count
and d.el_char <> c.el_char
and d.el_char <> b.el_char
and d.el_char <> a.el_char
left outer join bwtest e on e.el_count = a.el_count
and e.el_char <> d.el_char
and e.el_char <> c.el_char
and e.el_char <> b.el_char
and e.el_char <> a.el_char
group by a.el_count
order by 1

 

[TopJack]

That is a really interesting way to solve the problem - I will give it a try. Thanks for your time.

 

[r937]

topjack, if by chance you are using mysql, there's a wonderful and simple function you could use to give you exactly what you want

r937.com | rudy.ca

 

[Fahtrim]

Also if you are using Oracle there are some analytic functions that you could look into.

With the above, you will notice that if you take off the group by function you are returning well over 100 rows. This query could be a very bad performer very quickly if you have very many rows in the table.

An index on el_count would be the only thing that could possibly help this.

This would be relatively easy to do in a language such as RPG etc as I mentioned above. You could do this even with CLP.......

 

[TopJack]

It just so happens that it is an Oracle database that I'm SQL against. I didn't realise there was so many in-built analytical functions provided by Oracle.

Some interesting ones are LAG and LEAD that can look to previous and next records based upon the current record. This could open up possibilities for my problem.

Thanks Fahtrim and r937 for the thinking. Are any of you experienced in using the Oracle functions?

 

[r937]

no, sadly i am not

would you kindly report back to this thread if you do find a working solution?

i would like to see how it stacks up to mysql's GROUP_CONCAT function

r937.com | rudy.ca

 

[Fahtrim]

In pure SQL, it's not possible I think. But you could create your own function like this:
SQL> desc t
Name Null? Type
------------------------------- -------- ----
ID NUMBER
VAL NUMBER

SQL> select * from t;

ID VAL
---------- ----------
1 1
1 2
1 3
1 7
2 5
2 8
3 1
3 3
4 1

9 rows selected.

SQL> ed
Wrote file afiedt.buf

1 create or replace function concat_val(p_id in number)
2 return varchar2
3 is
4 cursor c_val
5 is
6 Select val
7 From t
8 Where id = p_id;
9 v_return varchar2(2000);
10 begin
11 for rec in c_val
12 loop
13 v_return := v_return||'-'||rec.val;
14 end loop;
15 v_return := substr(v_return,2);
16 return v_return;
17* end;
SQL> /

Function created.

SQL> Column TEST format a50
SQL> Select Distinct id
2 , concat_val(id) TEST
3 From t;

ID TEST
---------- --------------------------------------------------
1 1-2-3-7
2 5-8
3 1-3
4 1

Something like that perhaps.

 

[TopJack]

Thanks Fahtrim.

If I find any other solutions I will post back.

 

[TopJack]

Hello again.

As promised here is the final solution I used. I was lucky because I could use the Oracle (9) functionality on my database. I believe there is even more 'options' in the latest Oracle version. A sub query and record counting allows this to work with the hierarchical command sys_connect_by_path.

SELECT number,
LTRIM(SYS_CONNECT_BY_PATH(letter,','),',') AS concat

FROM (SELECT number, letter, 
ROW_NUMBER() OVER (PARTITION BY number ORDER BY letter) rn,
COUNT(*) OVER (PARTITION BY number) cnt
FROM database)

WHERE LEVEL = cnt

CONNECT BY rn = PRIOR rn+1 AND number = PRIOR number

START WITH rn = 1;

Any comments ?

Thanks again for your help.