Please help me incorporate a subquery

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 :slight_smile:

Never mind, I stuck the ‘subquery’ into a user-defined function and called that instead :slight_smile: