SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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).

    Code sql:
    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.

    Code sql:
    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
    Sam Hastings

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind, I stuck the 'subquery' into a user-defined function and called that instead
    Sam Hastings


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •