Dividers to the right, please.

Oracle wizards...

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.
Permalink JoC 
December 14th, 2011 4:11pm
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.
Permalink JoC 
December 14th, 2011 4:12pm
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.
Permalink Send private email Wayne! 
December 14th, 2011 4:14pm
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.
Permalink JoC 
December 14th, 2011 4:16pm
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 ';'
Permalink CircusAttraction 
December 14th, 2011 4:18pm
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.
Permalink Idiot 
December 14th, 2011 4:23pm
>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
Permalink df 
December 14th, 2011 4:24pm
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.
Permalink JoC 
December 14th, 2011 4:41pm
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.
Permalink CircusAttraction 
December 14th, 2011 4:53pm
Yes it is. Probably always < 10.

It's generally 1, but every now and then 2.
Permalink JoC 
December 14th, 2011 4:55pm
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.
Permalink JoC 
December 14th, 2011 5:05pm
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.
Permalink df 
December 14th, 2011 5:10pm
> 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.
Permalink Send private email Wayne! 
December 14th, 2011 5:15pm
Is LISTAGG what you want?
Permalink Synodontis 
December 14th, 2011 5:16pm
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
Permalink CircusAttraction 
December 14th, 2011 5:18pm
Various solutions depending on your version of Oracle:

http://www.oracle-base.com/article/misc/StringAggregationTechniques.php
Permalink CircusAttraction 
December 14th, 2011 5:24pm
WTF?  There's supposed to be an "s" in that URL:

Corrected:

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
Permalink CircusAttraction 
December 14th, 2011 5:28pm
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.
Permalink JoC 
December 14th, 2011 5:35pm
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.
Permalink JoC 
December 14th, 2011 5:39pm
OMG, that reminds me I gotta go do Christmas shopping.
Permalink trollop 
December 14th, 2011 5:41pm
"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.
Permalink Idiot 
December 14th, 2011 5:46pm
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.
Permalink JoC 
December 14th, 2011 5:49pm
> 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.
Permalink Q 
December 14th, 2011 7:30pm
LISTAGG.

Just discovered this this year.
Permalink Shylock 
December 15th, 2011 6:08am
Oh. Someone beat me to it.
Permalink Shylock 
December 15th, 2011 6:08am
Yeah unfortunately.... 11g.

But at least it's there. We'll get to it sooner or later.
Permalink JoC 
December 15th, 2011 1:17pm
There's also WM_CONCAT

http://psoug.org/reference/undocumented.html
Permalink Shylock 
December 15th, 2011 1:42pm

This topic is archived. No further replies will be accepted.

Other topics: December, 2011 Other topics: December, 2011 Recent topics Recent topics