I spend too long on finding the solution for this problem…
Basically I had a table which looked like this
What I wanted was to select all as one row as a set of items separated with a comma, where an item was ‘id: name’. The items had to be sorted by the id. So the result was supposed to look as below:
Solution
Initially I tried to write one SELECT:
SELECT
STUFF
(
(
SELECT ', ' + cast(id AS NVARCHAR(max)) + ': ' + name
FROM People
ORDER BY Id
FOR XML PATH ('')
),1,1,''
)
The problem here was FOR XML PATH escaped characters that could break the XML (e.g. ‘<' was replaced with '<'), which was not acceptible for me.
I looked for another solution but whatever I tried didn't work because of the ORDER BY statement which can't be used in a subquery or inline view.
Finally I decided to use a temporary table filling it with correct data and then create the final result. Here is the code:
DECLARE @res NVARCHAR(max) SET @res = '' CREATE TABLE #Result ( item nvarchar(max) ) INSERT INTO #Result SELECT CAST(Id AS NVARCHAR(max)) + ': ' + Name AS item FROM People ORDER BY id SELECT @res = @res + item + ', ' from #Result SELECT substring(@res,1,len(@res)-1) as Result
If you find a better solution can you please post it as a comment? I could use in in future 
Thanks in advance!
You could always stream it as XML Data and convert it into one column that way, similar to what you started with:
ISNULL(LTRIM(RTRIM(REPLACE(
(
SELECT CAST(ID AS NVARCHAR(MAX)) + ‘: ‘ + NAME AS [data()]
FROM People
ORDER BY ID
FOR XML PATH (”)
), ”, ‘,’))), ”)
For instances where it might “replace” strings with things like >… you could always encapsulate in a REPLACE string to change it back?
I use this trick a lot, but all the data I pass to it is alphanumeric with no symbols or unicode characters.
So true, you can always replace parts of the string… Sometimes easy solutions are the hardest to find
Cool, thanks for this guys, I got stuck with something like this a while back. I could not do it with SQL, I had to move the formatting to my application.
— HOW ABOUT USING CURSORS??:
— SELECT ONE COLUMN —
DECLARE @colvalu varchar (150),
@cString1 varchar (MAX),
@cString2 varchar (MAX)
SET @cString2 = ”
DECLARE my_cursor CURSOR FOR
select distinct COLNAME from TEBLENAME
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @colvalu
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cString1 = @colvalu + ‘,’
FETCH NEXT FROM my_cursor
INTO @colvalu
SET @cString2 = @cString2 + @cString1
END
SET @cString2 = Left(@cString2, Len(@cString2)-1)
print @cString2
CLOSE my_cursor
DEALLOCATE my_cursor
— SELECT 2(+) COLUMNS —
DECLARE @colvalu_1 varchar (150),
@colvalu_2 varchar (150),
@cString1 varchar (MAX),
@cString2 varchar (MAX)
SET @cString2 = ”
DECLARE my_cursor CURSOR FOR
SELECT COLNAME_1, COLNAME_2 FROM DBNAME
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @colvalu_1, @colvalu_2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cString1 = @colvalu_1 + @colvalu_2 + ‘,’
FETCH NEXT FROM my_cursor
INTO @colvalu_1, @colvalu_2
SET @cString2 = @cString2 + @cString1
END
SET @cString2 = Left(@cString2, Len(@cString2)-1)
print @cString2
CLOSE my_cursor
DEALLOCATE my_cursor
Cursors are an option here but I didn’t want to use them. Not only is it (much) longer but also not that efficient (especially if a table holds lots of data).