I’ve got the following query which supposedly emulates MySQL’s handy GROUP_CONCAT function (I’m working with SQL Server 2005, btw).
SELECT SUBSTRING(
( SELECT ', ' + c.category
FROM categories c
INNER
JOIN exhibitor_categories ec
ON c.category_id = ec.category_id
AND ec.exhibitor_id = 21
ORDER
BY c.category FOR XML PATH('')
), 3, 1000 ) AS categories
I would like to somehow incorporate this into the following query which generates a list of exhibitors, stand numbers, and hopefully categories which directly outputs its results into CSV format.
SELECT e.company_name AS [Our Company Name]
, l.company_name AS
[Listing Company Name]
, COALESCE(s.stand_no, 'TBC') AS [Stand number]
, l.tel AS [Telephone]
, l.url AS [Website address]
, l.description AS [Description]
, COALESCE(l.confirmed, 0) AS [Completed]
FROM exhibitors e
LEFT
JOIN exhibitor_showguide_listings l
ON e.exhibitor_id = l.exhibitor_id
INNER
JOIN exhibitor_stands es
ON e.exhibitor_id = es.exhibitor_id
AND es.show_id = ( SELECT TOP 1 show_id
FROM shows
WHERE start_date > GETDATE()
ORDER
BY start_date )
LEFT
JOIN stands s
ON es.stand_no = s.stand_no
AND es.show_id = s.show_id
ORDER
BY e.company_name;
Usually I’d take care of result concatenation in my application logic but the results of this SQL query are being output directly into CSV so I’d like to avoid having to do this if at all possible.
Any input would be much appreciated