SQL Snippet: Return Comma Delimited String

If you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This first example, shows how to return a comma delimited string of vlues from a select instead of the usual multiline recordset:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @DOCDATE DATETIME = '2017-04-12'
SELECT (STUFF((
        SELECT
			', ' + RTRIM(CNTRLNUM)
        FROM
			PM00400
        WHERE
			DOCDATE = @DOCDATE
        ORDER BY
			CNTRLNUM
        FOR XML PATH('')
        ), 1, 2, '')
    ) AS ReturnString

The example above, is created against the Microsoft Dynamics GP sample database and returns a comma delimited list of vouchers for a particular date.