converting rows to one column sql server

sql, sql-server-2008

Solution

Since you're keeping the final comma in your example, you don't need the STUFF, especially if you're using it with parameters `,1,0,''` which is a no-op.

SELECT
    (SELECT NAME + ','
       FROM TABLE1
        FOR XML PATH(''),type).value('.','nvarchar(max)') AS [ACCOUNT NAMES]

If you did want to remove the last comma, then it should be like this:

SELECT STUFF(
    (SELECT ',' + NAME
       FROM TABLE1
        FOR XML PATH(''),type).value('.','nvarchar(max)'),1,1,'') AS [ACCOUNT NAMES]

Note: The problem is that you're using a common pattern that is almost as old as the internet, which did not account for XML entitisation. Using the `FOR XML TYPE` specifier keeps the original text when extracted again via `.value, varchar(max)`.

Problem

i am using the following query to convert rows to column ``` SELECT STUFF((SELECT NAME + ',' FROM TABLE1 FOR XML PATH('')),1,0,'') AS [ACCOUNT NAMES] ``` the table have the follwing data ``` NAME ------------------- GURPREET & CO. SIMRAN TRADERS LABOUR & WELFARE ------------------- ``` i want the output like GURPREET & CO., SIMRAN TRADERS, LABOUR & WELFARE, but the sql given the output GURPREET &amp CO., SIMRAN TRADERS, LABOUR &amp WELFARE, how can i remove &amp?

Original source