0

Possible Duplicate:
SQL Server: Can I Comma Delimit Multiple Rows Into One Column?

I have a bunch of values in a data source that i am trying to output as one single row:

SELECT
    '|| Id == "' + cast(ID as varchar(100)) + '"'
FROM 
    dbo.CAItem
WHERE 
    isactive = 1

This is the correct data, but I would like to output it as a single row instead of individual rows for each result so that the my return would be one single row like this:

|| Id == "4431" || Id == "4436"
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
some_bloody_fool
  • 4,605
  • 14
  • 37
  • 46
  • If this is display only, why can't you do this application side? Although, at minimum, you're going to need some sort of delimiter. – Clockwork-Muse Feb 29 '12 at 20:24

1 Answers1

1

There is no GROUP_CONCAT() function in SQL Server, however STRING_AGG() was introduced in SQL Server 2017.

In the meantime, you can do this:

DECLARE @a TABLE(ID INT);

INSERT @a SELECT 4431 UNION ALL SELECT 4436;

SELECT *
  FROM (SELECT '|| Id == "' + CONVERT(VARCHAR(100), ID) + '"'
  FROM @a -- WHERE Isative = 1
FOR XML PATH('')) AS x(n);
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490