A useful SQL Tip - Coalesce.

I do a bit of Reporting Services work from NAV databases and recently a client wanted a list of Names to be presented as a comma delimited list.

Now I knew I had done this before and there was a really smart way of doing it but it took me a while to find it again. The trick is to use the coalesce function. You need to declare a variable to select into and then select from that so this is for use in a table-function or stored procedure.

Try this out in a NAV database when you have more than once company.

DECLARE @CompanyList VARCHAR(1000)

SELECT @CompanyList = COALESCE(@CompanyList + ', ', '') + Name
FROM Company

SELECT @CompanyList