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
-
1@mohamadreza, based on your comment you intend to wrap this stackoverflow.com/questions/2661437/… in a function. However if you do and you use it in a select list of a query, it will be very inefficient. There are much better ways to handle that, see my answer for an example of how.KM.– KM.2010-04-19 13:49:54 +00:00Commented Apr 19, 2010 at 13:49
Add a comment
|
3 Answers
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
1 Comment
mhmd
That's exactly what I need. thanks. also varchar(max) cannot be used for a sql function argument so I used varchar(500) for example
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.
Comments
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
