Re: SQL Query: Concatenation of multiple records

nickheppleston_at_gmx.co.uk (Nick Heppleston) wrote in message news:<150151d5.0112180327.6928b3ad_at_posting.google.com>...
> Trying to solve a query which is completely beyond me. Any help would
> be greatly appreciated.
>
> We have 2 tables, the first a product table with each record relating
> to an individual product. The second table is a 'Long Description'
> table with multiple entries per product code (relating to a unique ID
> in the first table). So, for product '00ASD23' the long description is
> broken down into say five different records each with the product code
> '00ASD23'.
>
> The query needs to pull back individual records for each product (as
> in this first table), however each record must also contain the long
> description, made up from multiple records in the second table. These
> records must be in essence concatenated with each other and returned
> as one string that can be included into each individual product
> record.
>
> Again, any help would be greatly appreciated. If you require more
> information, please don't hesitate to contact me or reply to this
> posting.
>
> Cheers, Nick
> nick.heppleston_at_midwich.com

1- Use pl/sql as this would be easy as long as the total length of the descriptions does not exceed 32K (max. length varchar2 in plsql).

2- If you have to use pure sql then here is one possibility if the second table has a position indicator of some type on the order of the descriptions for a product key and the maximum number of descripts is small then you can do something like:

select a.x, b.desc||c.desc||d,desc
from a, b, c, d

where  a.key = b.key(+)
and    a.key = c.key(+)
and    a.key = d.key(+)
and    b.position = 1
and    c.position = 2
and    d.position = 3

It seems like I know a better way to do this, but right now this is all that comes to mind.

• Mark D Powell --