[thelist] [SQL] tough concatenation

[Frank]

>I have a 1-M relationship between widgets and widget parts. For a given
>widgetID, I'd like to return a string of all the widget parts. So, if this
>is true:
>
>SQL> select widgetparts from widgets where widgetid=4;
>
>.-------------.
>| widgetparts |
>|-------------|
>|   handle    |
>|    body     |
>|   gipode    |
>|   carpet    |
>.-------------.
>
>
>I want to get it to return:
>
>handle,body,gipode,carpet
>
>through SQL. I do *NOT* want to iterate through the results and make my own
>string.

Just a guess, but you could use the concatenation operator.

In MySQL there's a function known as concat

SELECT CONCAT(handle, ',' , body, ',' , gipode, ',' , carpet)
FROM widgetparts
WHERE widgetid-4;

Surely SQL7 has some sort of concatenation function? Mind you these 
sort of things reduce the portability of your code to next to 
nothing...
-- 

"Fate is just the weight of circumstances."

Frank Marion                      Loofah Communications
frank at loofahcom.com               http://www.loofahcom.com