I want to select some values and have all of it concatted so the result is just a single row/col of one string.

Is there anyway to do that without writing a function, looping a cursor and building the string myself?

I'm wanting something like SELECT JOIN(<colname>, '; ')... where the second argument would be an optional delimiter inserted between values.

I've completely fabricated the JOIN function above, don't let it mind screw you, I don't mean anything like the regular context of inner/outer joins.

In MySQL, it's called GROUP_CONCAT().  It frustrates me how much functionality exists in "toy" databases but in Oracle or SQL Server you have to use some terrible hack to accomplish the same thing.

Sorry I can't help you with Oracle.  In SQL Server you can use the XML facilities to accomplish this.

Ahhh, I just had to find the magic words to google, that helped.

For anyone else wanting the same in oracle, google the COLLECT keyword.

Are you spooling out to a file using SqlPlus or does it need to be part of the SQL itself?

In SqlPlus you can:

SET colsep ';'

GROUP_CONCAT is supported by SQLite as well.

http://www.sqlite.org/lang_aggfunc.html

SQLite is pretty awesome, you know you can write custom view triggers, right? Much better than what MYSQL provides.

>In SQL Server you can use the XML facilities to accomplish this.

Much simpler in SQL Server (no idea if this works in Oracle).

DECLARE @result varchar(max)
SET @result = ''

SELECT @result = @result + colname + ';' FROM sourceTable

As it turns out, collect is a poorly implemented good idea. I guess it saves someone the trouble of writing the cursor loop I mentioned.

My problem is that I want this to happen in a view.

Their collect implementation requires you to cast the result to a type you've previously declared. Bah.

Is the maximum number of rows you wish to convert to a single concatenated column a small number (i.e. 3 - 10 values)?

I possibly have a solution for your view if the possible number of values is relatively small.

Yes it is. Probably always < 10.

It's generally 1, but every now and then 2.

Oh wow.... seriously.

Someone smarter look at COLLECT().

I'm not sure WTF the point was. You still have to write pretty much exactly the same code you always did before.

All they seem to have done is replace the cursor loop with a loop through the values you collect().

This makes no sense.

It looks as ugly as T-SQL's CTE functionality.

On this topic, though, why do you need to do what you are asking? This is one of those "sounds like you're going down the wrong path" sort of things.

> Much simpler in SQL Server

Yes, that would work in this situation (you only want one row) but that's not a general purpose solution to lack of GROUP_CONCAT() in T-SQL.

Is LISTAGG what you want?

Here's one SQL which looks a little messy:

select
  b.key,
  /* combines up to 10 distinct values per b.key */
  max(case when r=1 then a.val||';' end) ||
  max(case when r=2 then a.val||';' end) ||
  max(case when r=3 then a.val||';' end) ||
  max(case when r=4 then a.val||';' end) ||
  max(case when r=5 then a.val||';' end) ||
  max(case when r=6 then a.val||';' end) ||
  max(case when r=7 then a.val||';' end) ||
  max(case when r=8 then a.val||';' end) ||
  max(case when r=9 then a.val||';' end) ||
  max(case when r=10 then a.val||';' end) combo
from
( /* create index value for each distinct val within "data table" */
select rownum r,val from (
select distinct val from (
  select 'Foo'key,'10'val from dual union
  select 'Foo'key,'20'val from dual union
  select 'Fizz'key,'10'val from dual union
  select 'Buzz'key,'42'val from dual union
  select 'Buzz'key,'bleeb'val from dual union
  select 'Bar'key,'x16'val from dual union
  select 'Bar'key,'x32'val from dual
))) A
join
( /* original data table */
  select 'Foo'key,'10'val from dual union
  select 'Foo'key,'20'val from dual union
  select 'Fizz'key,'10'val from dual union
  select 'Buzz'key,'42'val from dual union
  select 'Buzz'key,'bleeb'val from dual union
  select 'Bar'key,'x16'val from dual union
  select 'Bar'key,'x32'val from dual
) B
on B.val = A.val
group by
  b.key
;

And here's the result:

KEY  COMBO
---- ---------
Bar  x16;x32;
Buzz 42;bleeb;
Fizz 10;
Foo  10;20;

Here's another SQL that only supports up to two distinct values per key:

select
  key, min_val || case when min_val!=max_val then ';'||max_val end as combo
from
(
select
  b.key,
  /* combines up to 2 distinct values per b.key */
  min(val) as min_val,
  max(val) as max_val
from
( /* original data table */
  select 'Foo'key,'10'val from dual union
  select 'Foo'key,'20'val from dual union
  select 'Fizz'key,'10'val from dual union
  select 'Buzz'key,'42'val from dual union
  select 'Buzz'key,'bleeb'val from dual union
  select 'Bar'key,'x16'val from dual union
  select 'Bar'key,'x32'val from dual
) B
group by
  b.key
) A
;

And the result:

KEY  COMBO
---- ----------
Bar  x16;x32
Buzz 42;bleeb
Fizz 10
Foo  10;20

Various solutions depending on your version of Oracle:

http://www.oracle-base.com/article/misc/StringAggregationTechniques.php

WTF?  There's supposed to be an "s" in that URL:

Corrected:

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

Yeah I figured I might get called out on that.

It's because they've changed the implementation of fields which were once one-to-one relations to fields which now have one-to-many relations.

So say it's a list of airplanes. At one point, all planes were single engine so the engine column just showed the engine. Now, maybe it has 1, maybe 10, and it isn't such that 10 columns would be acceptable.

Ultimately, all of it boil down to the stink given off when you get a bunch of people close to a deadline and they are still figuring out business requirements and changing the damned data model.

Hey synodontis, thanks!

That's pretty much exactly what I wanted, I think.

It makes me wtf at collect() even more, but I bet that's mostly my own ignorance about oracle and custom types inside of it.

OMG, that reminds me I gotta go do Christmas shopping.

"Synodontis is the largest genus of catfishes (order Siluriformes) of the family Mochokidae. Synodontis are also known as squeakers, due to their ability to make stridulatory sounds through their pectoral fin spines when handled or disturbed."

Ok yeah, those squeaking guys. Interesting.

Haha... I started yesterday and all I got was gift cards for people who are pretty much strangers.

I'm considering just bah humbugging and giving everyone a netflix subscription. My logic is that it's now probably a bit overpriced so they might not buy it for themselves.

Well, that, and crowded streets and stores get under my skin like those scarab beetles in the mummy.

> Well, that, and crowded streets and stores get under my
> skin like those scarab beetles in the mummy.

I did most of my Christmas shopping online.

LISTAGG.

Just discovered this this year.

Oh. Someone beat me to it.

Yeah unfortunately.... 11g.

But at least it's there. We'll get to it sooner or later.

There's also WM_CONCAT

http://psoug.org/reference/undocumented.html