group results into a string

how to group results from a select statement with single column into a comma
separated string? e.g.
results
--
a
b
c
...
string:
a, b, c, ...nono
I suggest you doing such operations on the client side
create table #test
(
col char(1)
)
insert into #test values ('a')
insert into #test values ('b')
insert into #test values ('c')
declare @.str varchar(20)
set @.str=''
select @.str=@.str+coalesce(col,'')+';' from #test
select @.str
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BFA2B825-A041-4775-9692-1D1C5E0490AE@.microsoft.com...
> how to group results from a select statement with single column into a
comma
> separated string? e.g.
> results
> --
> a
> b
> c
> ...
> string:
> a, b, c, ...|||how can I do that in SQL Server?
"Uri Dimant" wrote:

> nono
> I suggest you doing such operations on the client side
> create table #test
> (
> col char(1)
> )
> insert into #test values ('a')
> insert into #test values ('b')
> insert into #test values ('c')
> declare @.str varchar(20)
> set @.str=''
> select @.str=@.str+coalesce(col,'')+';' from #test
> select @.str
>
> "nonno" <nonno@.discussions.microsoft.com> wrote in message
> news:BFA2B825-A041-4775-9692-1D1C5E0490AE@.microsoft.com...
> comma
>
>|||I did show you , did not I?
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:C9E42970-F176-4B03-ADA2-7ABFCEC1B944@.microsoft.com...
> how can I do that in SQL Server?
> "Uri Dimant" wrote:
>|||Thx Uri! It's really fantastic! Can u tell me what's the coalesce function
used for?
"Uri Dimant" wrote:

> I did show you , did not I?
>
> "nonno" <nonno@.discussions.microsoft.com> wrote in message
> news:C9E42970-F176-4B03-ADA2-7ABFCEC1B944@.microsoft.com...
>
>|||BOL says:
COALESCE
Returns the first nonnull expression among its arguments
Fore more details please refer to the BOL.
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:DB38C965-1514-4560-97ED-0C36AB7DC2F8@.microsoft.com...
> Thx Uri! It's really fantastic! Can u tell me what's the coalesce function
> used for?
> "Uri Dimant" wrote:
>
into a|||> Thx Uri! It's really fantastic!
Note that this method is also not supported, and does not guarantee correct
results in all possible cases.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Uri,
Would you mind mentioning the potential drawbacks of this method while
suggesting it, at least in the future? Several newbies with limited SQL
exposure tend to misunderstand such constructs as a valid SQL queries and
may even pass onto others as a recommended approach.
Anith|||Anith
If you read my post carefully I did mention what I would have done in such
situations

>I suggest you doing such operations on the client side
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23lPkKPqPFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Uri,
>
> Would you mind mentioning the potential drawbacks of this method while
> suggesting it, at least in the future? Several newbies with limited SQL
> exposure tend to misunderstand such constructs as a valid SQL queries and
> may even pass onto others as a recommended approach.
> --
> Anith
>|||>> If you read my post carefully I did mention what I would have done in
Ah..I did not notice that, my apologies :-(
Anith