Concatenating text from multiple rows

[Six Fried Rice]

I have a table "CUSTOM_FUNCTION" with an ID, and another table  
"CUSTOM_FUNCTION_PARAMETER" with a foreign key "ID_CUSTOM_FUNCTION"  
such that each CUSTOM_FUNCTION record has 0 or more associated  
CUSTOM_FUNCTION_PARAMETER records.

In one situation, it would be exceptionally handy to concatenate all  
associated values from a VARCHAR column in the  
CUSTOM_FUNCTION_PARAMETER table into a single value in a result set  
with one row per CUSTOM_FUNCTION. For instance, suppose I have:

CUSTOM_FUNCTION.ID = 1
CUSTOM_FUNCTION.NAME = "Volume"

CUSTOM_FUNCTION_PARAMETER.ID_CUSTOM_FUNCTION = 1
CUSTOM_FUNCTION_PARAMETER.NAME = "length"

CUSTOM_FUNCTION_PARAMETER.ID_CUSTOM_FUNCTION = 1
CUSTOM_FUNCTION_PARAMETER.NAME = "width"

CUSTOM_FUNCTION_PARAMETER.ID_CUSTOM_FUNCTION = 1
CUSTOM_FUNCTION_PARAMETER.NAME = "height"

I would like a single SQL query that returns a single row like this:

NAME: Volume
PARAMS: length; width; height

Where the "params" result column is a VARCHAR with all three parameter  
names concatenated, with semicolon's in between.

In MySQL, I would accomplish this with the odd-but-handy GROUP_CONCAT  
function, along these lines:

select
   F.NAME as NAME,
   GROUP_CONCAT(P.NAME, "; ") as PARAMS
from
   CUSTOM_FUNCTION F
   left join CUSTOM_FUNCTION_PARAMETER P on P.ID_CUSTOM_FUNCTION = F.ID
group by F.ID

I know that isn't standard, but I'm wondering if there is any clever  
approach in Derby to accomplish the same thing. I've been trying to  
dream something up, but with no success so far. Of course I know I can  
get the same effect by processing the result set on the Java side, but  
for various reasons, it would be much more convenient in this case to  
let Derby do it for me.

Any ideas would be appreciated.

Thanks,

Geoff

[Rick Hillegas-2]

Hi Geoff,

This sounds like a good use for a user-defined aggregate (DERBY-672).
Most of the machinery for this feature actually exists inside Derby. We
would have to agree on some syntax for declaring user-defined aggregates
since ANSI doesn't have language for this. I'd be happy to coach someone
through the work.

Another comment inline...

Six Fried Rice wrote:

> I have a table "CUSTOM_FUNCTION" with an ID, and another table
> "CUSTOM_FUNCTION_PARAMETER" with a foreign key "ID_CUSTOM_FUNCTION"
> such that each CUSTOM_FUNCTION record has 0 or more associated
> CUSTOM_FUNCTION_PARAMETER records.
>
> In one situation, it would be exceptionally handy to concatenate all
> associated values from a VARCHAR column in the
> CUSTOM_FUNCTION_PARAMETER table into a single value in a result set
> with one row per CUSTOM_FUNCTION. For instance, suppose I have:
>
> CUSTOM_FUNCTION.ID = 1
> CUSTOM_FUNCTION.NAME = "Volume"
>
> CUSTOM_FUNCTION_PARAMETER.ID_CUSTOM_FUNCTION = 1
> CUSTOM_FUNCTION_PARAMETER.NAME = "length"
>
> CUSTOM_FUNCTION_PARAMETER.ID_CUSTOM_FUNCTION = 1
> CUSTOM_FUNCTION_PARAMETER.NAME = "width"
>
> CUSTOM_FUNCTION_PARAMETER.ID_CUSTOM_FUNCTION = 1
> CUSTOM_FUNCTION_PARAMETER.NAME = "height"
>
> I would like a single SQL query that returns a single row like this:
>
> NAME: Volume
> PARAMS: length; width; height
>
> Where the "params" result column is a VARCHAR with all three parameter
> names concatenated, with semicolon's in between.
>
> In MySQL, I would accomplish this with the odd-but-handy GROUP_CONCAT
> function, along these lines:
>
> select
>   F.NAME as NAME,
>   GROUP_CONCAT(P.NAME, "; ") as PARAMS
> from
>   CUSTOM_FUNCTION F
>   left join CUSTOM_FUNCTION_PARAMETER P on P.ID_CUSTOM_FUNCTION = F.ID
> group by F.ID
>
> I know that isn't standard, but I'm wondering if there is any clever
> approach in Derby to accomplish the same thing. I've been trying to
> dream something up, but with no success so far. Of course I know I can
> get the same effect by processing the result set on the Java side, but
> for various reasons, it would be much more convenient in this case to
> let Derby do it for me.

I don't understand the distinction you are drawing between Derby and the
Java side. Derby functions let you run your Java code inside the
database. You can create a user-defined function which loops through
custom_function_parameter inside your query. Then you can write a master
query like this:

select name, concatenateFunctionParameters( id )
from custom_function

In the Derby demo code, you can find an example of how to code and use
one of these aggregating functions. Just grep the demo code for
"getMedianTestScore".

Hope this helps,
-Rick
>
> Any ideas would be appreciated.
>
> Thanks,
>
> Geoff

[Six Fried Rice]

On Jun 30, 2008, at 6:18 AM, Rick Hillegas wrote:

> I don't understand the distinction you are drawing between Derby and  
> the Java side. Derby functions let you run your Java code inside the  
> database. You can create a user-defined function which loops through  
> custom_function_parameter inside your query. Then you can write a  
> master query like this:
>
> select name, concatenateFunctionParameters( id )
> from custom_function
>
> In the Derby demo code, you can find an example of how to code and  
> use one of these aggregating functions. Just grep the demo code for  
> "getMedianTestScore".

Rick:

Thanks again for your quick and clear response. I just didn't know  
about this feature. I meant I didn't want to process the JDBC result  
set in my application to get the result, but doing what you describe  
above in Java sounds perfect.

I'm still a derby newbie, so my head is a little in the MySQL way. In  
MySQL, you can define your own functions in C but of course you have  
to compile them into the server. I was hoping not to have to do that  
sort of thing, but if I can write a function as a drop in as you  
describe, then I'm in great shape.

Again, *thank you*.

Geoff