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
