Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Message-Id: <3DD376AD-B003-462A-81B2-1688E74793C2@sixfriedrice.com>
From: Six Fried Rice <technet@sixfriedrice.com>
To: Derby Discussion <derby-user@db.apache.org>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v924)
Subject: Concatenating text from multiple rows
Date: Mon, 30 Jun 2008 00:07:20 -0700

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