how to return multiple rows of a single column separated by a ',' or html break in TSQL?

Question

the sql query :

SELECT coloumn1
FROM tablex
WHERE id=10
and state='COL'

lets assume the above query retuns the following

coloumn1
---------
abc
def
geh
ijk

i need the result to be like this

coloumn1
---------
abc, def, geh, ijk

OR

coloumn1
---------
abc<br/>def<br/>geh<br/>ijk

how can achive a result set like the above? PLZ!help.

Answer 1

Try this out:

Select Top 1 
substring(
    (SELECT coloumn1 FROM tablex WHERE id=10 and state='COL' for XML PATH( '' )), 
    0,
    Len((SELECT coloumn1 FROM tablex WHERE id=10 and state='COL' for XML PATH( '' ))) - 2
) 

from tablex

Answer 2

Try this -

DECLARE @varr nvarchar(1000)

SELECT 
    @varr = COALESCE(@varr + ',', '') + column1
FROM 
    tablex
WHERE
     id=10 
     and state='COL' 

PRINT @varr

Answer 3

DECLARE @list varchar(100);

SELECT COALESCE( @list + ', ', '') + CAST(Column1 as varchar(3))
FROM tablex
WHERE id = 10

Doh Sachin Shanbhag beat me to it