Copy link to clipboard
Copied
i have 2 simple tables
1 - cars
id make model
1 toyota corolla
2 - car_images
rel_car_id image
1 front.jpg
1 back.jpg
i need a 1 line query with the images all in 1 field (comma seperated)
eg
1 toyota corolla front,jpg,back.jpg
ive tried complex sql, valuelist function and everything has be beat!
im stuck and woudl love your help.
ps my final goal is to take this new query and dump it to a csv.
Copy link to clipboard
Copied
Something like this?
SELECT c.make, c.model, i.image
FROM cars c
LEFT JOIN car_images i
ON c.id = i.rel_car_id
WHERE c.id = 1
Ken Ford
Copy link to clipboard
Copied
DECLARE @tmpstr varchar(250) -- or max or whatever
SELECT c.make, c.model, @tmpstr = COALESCE( @tmpstr + ',' , '') + i.image as
'images'
FROM cars c
LEFT JOIN car_images i
ON c.id = i.rel_car_id
WHERE c.id = 1
Copy link to clipboard
Copied
yui8979 thanks,
I am getting close, im am not skilled on this and this looks like a stored procedure?
Where do i put this to run this.
I get an error when i create a new proceduure in SQL 2005 express.
Is there a way I can code this from the coldfusion page?
Copy link to clipboard
Copied
yes you simply put that whole chunk into your cfquery. Edit : There was a minor error in the previous solution
<cfquery name="test" datasource="your-datasource-here">
DECLARE @tmpstr varchar(250) -- or max or whatever
DECLARE @make varchar(250)
DECLARE @model varchar(250)
SELECT @make=c.make, @model=c.model, @tmpstr = COALESCE( @tmpstr + ',' , '') + i.image as 'images'
FROM cars c
LEFT JOIN car_images i
ON c.id = i.rel_car_id
WHERE c.id = 1
SELECT @make,@model,@tmpstr
</cfquery>
Message was edited by: yui8979
Copy link to clipboard
Copied
Haha sorry, my bad. My code gives you an error.
Anyway, you can also do what Ken suggested :
<cfquery name="test" datasource="your-datasource">
SELECT c.id, c.make, c.model, i.image
FROM cars c
LEFT JOIN car_images i
ON c.id = i.rel_car_id
WHERE c.id = 1
order by c.id
</cfquery>
<cfoutput query=test group=id>
#id#,#make#,#model#<cfoutput>,#image#</cfoutput>
</cfoutput>
Message was edited by: yui8979
Copy link to clipboard
Copied
Thanks guys and sorry for my delay in returning to you.
I had to push the data to a csv file so I had to do the code at the source (sql server).
So its actually not really a CF query at all.
However here is the sql 2005 code that worked a treat, seems OTT for my liking but it works..
SELECT VEHICLES.ID AS VEHICLEID, '' AS REGISTRATION, MAKES.MAKE, VEHICLES.MODEL, '"' + REPLACE(VEHICLES.SUMMARY, '''', '\"') + '"' AS TITLE,
'' AS TRANSMISSION, VEHICLES.FUEL, '' AS BODYSTYLE, VEHICLES.PRICE, VEHICLES.YEAR, VEHICLES.MILEAGE, 0 AS DOORS, '' AS COLOUR,
'' AS INSGRP, '' AS LOCATION, '"' + REPLACE(VEHICLES.DESCRIPTION, '''', '\"') + '"' AS DESCRIPTION, IMAGES = '"' + REPLACE
((SELECT DISTINCT 'http://www.clivehamiltonmotors.com/images/vehicles/' + IMAGE + ',' AS [data()]
FROM VEHICLE_IMAGES
WHERE REL_VEHICLE_ID = VEHICLES.ID FOR XML PATH('')), '', '-') + '"'
FROM VEHICLES INNER JOIN
MAKES ON VEHICLES.MAKE = MAKES.ID
thanks again.
Copy link to clipboard
Copied
Since you're using SQL Server, if you need to do this sort of thing in the future take a look at .NET CLR functions. You can find a sample online for a concatenate aggregate function that does exactly what you're looking for.