How to format TSQL SELECT output in SQL Sever

Question

How to loop through a select statement results to have a formatted text? for example the select is like:

select name from table 

and we want a variable @names like this:

"name1,name2,name3"

Database is SQL Server 2005

Answer 1

This would need to be done within a function. There's no quick way to do this. Normally you would do this within your client side code.

Answer 2

If table contains several records, i.e.:

1, name1, ..
2, name2, ..
3, name3, ..

then this query:

DECLARE @names VARCHAR(MAX)
SELECT @names = COALESCE(@names + ', ', '') + name
FROM table

will produce next result:

name1, name2, name3

See COALESCE on MSDN

Answer 3

If you plan on making a function that you do on each row form another query it will be really slow, because the function needs to be called for each row. You should work with sets of data at one time, it does all rows at one time. This is an example of how to concatenate multiple values for each row of another query:

set nocount on;
declare @t table (id int, name varchar(20), x char(1))
insert into @t (id, name, x)
select 1,'test1', 'a' union
select 1,'test1', 'b' union
select 1,'test1', 'c' union
select 2,'test2', 'a' union
select 2,'test2', 'c' union
select 3,'test3', 'b' union
select 3,'test3', 'c' 

SELECT p1.id, p1.name,
          stuff(
                   (SELECT
                        ', ' + x
                        FROM @t p2
                        WHERE p2.id=p1.id
                        ORDER BY name, x
                        FOR XML PATH('') 
                   )
                   ,1,2, ''
               ) AS p3
      FROM @t p1
     GROUP BY 
        id, name

OUTPUT:

id          name                 p3
----------- -------------------- -----------
1           test1                a, b, c
2           test2                a, c
3           test3                b, c