SQL statement like GROUP_CONCAT

[suzgeep]

Hello... newbie here. I'm just learning SQL so bear with me please ....

I'm trying to find a way to do a "group_concat" function but it appears to be MySQL specific. Is there a way to do something similar but with Oracle SQL? I need to do this in straight SQL (no procedures).

Example:
Citizenship,Fvisa 000338854 PBAC
Citizenship,Fvisa 000338854 UGRD
Admissions,Testc 000341766 PBAC
Admissions,Testc 000341766 PBAC
Admissions,Testc 000341766 UGRD
Admissions,Testc 000341766 LAWD

Desired Output:
Citizenship,Fvisa 000338854 PBAC/UGRD
Admissions,Testc 000341766 PBAC/UGRD/LAWD

I've searched high and low for over an hour...
Any help appreciated.

 

[Dagon]

Try looking at ROLLUP and CUBE functions in Oracle (if you have the OLAP version).

 

[juliane26]

rollup and cube won't help you much, they are used for calculations, not for string concatinations.

this is no standard SQL and you need to write your own function/procedure to accomplish that.

may be you post it at the Oracle forum ?

Juliane

 

[dnoeth]

Standard SQL:1999, also available in recent Oracle versions:

select
  c1,c2,c3,
  max(case when rnk = 1 then        c4 else '' end) ||
  max(case when rnk = 2 then ',' || c4 else '' end) ||
  max(case when rnk = 3 then ',' || c4 else '' end) ||
  max(case when rnk = 4 then ',' || c4 else '' end) ||
  max(case when rnk = 5 then ',' || c4 else '' end) ||
  max(case when rnk = 6 then ',' || c4 else '' end) ||
  max(case when rnk = 7 then ',' || c4 else '' end) ||
  max(case when rnk = 8 then ',' || c4 else '' end) ||
/*** Indicating more than 8 rows ***/
  max(case when rnk > 8 then ',...' else '' end) as Columns
from
 (
  select
    c1,c2,c3,
   ,dense_rank() over (partition by   c1,c2,c3
                 order by c4) as rnk
  from mytab
 ) dt
group by c1,c2,c3

Dieter

 

[juliane26]

still, as long as you don't know how many you can have at maximum that is no solution.

Juliane