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 (http://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.