Re: SQL Query: Concatenation of multiple records

On 18 Dec 2001 03:27:39 -0800, nickheppleston_at_gmx.co.uk (Nick Heppleston) wrote:

>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

Just a thought. If the other table has a small maximum of rows that you can hardcode, and another identifer order the rows in the table:

SELECT

	Code
	|| (SELECT Text FROM Description WHERE Id = P.Id AND Row = 1)
	|| (SELECT Text FROM Description WHERE Id = P.Id AND Row = 2)
	|| (SELECT Text FROM Description WHERE Id = P.Id AND Row = 3)
	|| (SELECT Text FROM Description WHERE Id = P.Id AND Row = 4)
	|| (SELECT Text FROM Description WHERE Id = P.Id AND Row = 5)
FROM
	Product P;

Brian Received on Thu Dec 20 2001 - 07:47:09 CST