How to return unknown number of items back in onw row

[arnargud]

Hi

I have, hopefully, a quick question. In my database I have a table Product and a table Images.
The images has an id, product_id and image_name.
When I query for a specific product I want all the image_names associated with it in the query.... the catch.. on one line.
Now I get
123 productdesc1 image_name1
123 productdesc1 image_name2

When I want to get it like
123 productdesc1 image_name1 image_name2

The problem is that I don´t know how many images there will be

How can I solve this?

thanks
Arnar Gudnason

[Jim Doherty]

Hi

I have, hopefully, a quick question. In my database I have a table Product and a table Images.
The images has an id, product_id and image_name.
When I query for a specific product I want all the image_names associated with it in the query.... the catch.. on one line.
Now I get
123 productdesc1 image_name1
123 productdesc1 image_name2

When I want to get it like
123 productdesc1 image_name1 image_name2

The problem is that I don´t know how many images there will be

How can I solve this?

thanks
Arnar Gudnason

Assumptions
You have a table Called Product with Fields
ProductID (datatype - int identity)
Product (datatype - text)

You have a table called Images with Fields
ID (datatype int identity)
ProductID (datatype int)
Image_Name (datatype text)

Requirement
To retrieve all images from the Images table associated with a specific ProductID from the Product table and in so doing present the image names contained in the Images table concatenated as a continous single field line display separating the respective values with a comma and then a space.

Implementation method
Call the function from a query by typically using this SQL syntax

SELECT ProductID, UDF_RetrieveImages(ProductID) as Images FROM Product


Functional Resolution
Create a user defined function having the following syntax

 
CREATE FUNCTION dbo.UDF_RetrieveImages
( @ProductID int )
RETURNS varchar(500)
AS
BEGIN
DECLARE @ImageString varchar(255), @delimiter char
SET @delimiter = ','
SELECT @ImageString = COALESCE(@ImageString + @delimiter, '') +SPACE(1)+ Image_Name FROM .dbo.Images where ProductID=@ProductID order by ID
RETURN ( SELECT LTRIM(@ImageString) AS [Images])
END
 

Comments & considerations
Pay attention to the defined varchar lengths within the body of the function to ensure it adequately reflects your needs

The resultant SQL statement when run as a view/query outlined above should present you with what you require from your posting

Regards

Jim :)