Comma separated values in a query

I've blogged on this several times, but each time the solution has been a little unwieldly for one reason or another. 

The job is to take a set of rows:

 

And return a comma separated field. 

Solution below

Related Articles

... and you 'll find more on the SQL (General) Menu

I'm going to let the code speak for itself. If you have a question, please ask. 

SELECT 
        h.SOPNUMBE,
        (STUFF((SELECT CAST('; ' + RTRIM(l.itemnmbr) AS VARCHAR(MAX))
                FROM sop10200 l
                WHERE l.sopnumbe = h.sopnumbe
                FOR XML PATH ('')), 1, 2, '')
                 
            ) AS Holds
    FROM sop10100 h
    WHERE h.SOPNUMBE BETWEEN  'std10119400' AND 'std10119419'

 

 

SELECT sopnumbe, soptype, prchldid, HOLDDATE FROM sop10104 WHERE sopnumbe = 'STD10082252'
 
SELECT  h.sopnumbe, h.soptype,
            (STUFF((SELECT CAST(', ' + RTRIM(ho.prchldid)
                + '(' + FORMAT(ho.HOLDDATE,'M/d') +  ')' AS VARCHAR(MAX))
                FROM sop10100 hh
                    JOIN sop10104 ho ON ho.sopnumbe = hh.sopnumbe AND ho.soptype = hh.soptype
                WHERE hh.sopnumbe = h.sopnumbe
                FOR XML PATH ('')), 1, 2, '')
            ) AS Holds                       
    FROM sop10100 h
    WHERE h.sopnumbe = 'STD10082252          '

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences