CSV From SELECT query

6 Apr 2006 8:51 AM

John

Can I get a SELECT statement to return a comma seperated value list

eg
I have a table :
      ID ACT1
      1 Green
      1 Blue
      2 Green
      2 Red
      3 Red
      4 Black
      5 Green

Select ACT1 FROM tbl1 WHERE Id=1

I want to return Green, Blue

then link this to another table and as part of my result set show the comma
seperated values

6 Apr 2006 10:18 AM

Uri Dimant

John
Do such reports on the client side. In T-SQL it is not reliable .

create table w
(
id int not null,
t varchar(50) not null
)

insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w

create function dbo.fn_my ( @id int)
returns varchar(100)
as
begin
declare @w varchar(100)
   set @w=''
   select @w=@w+t+',' from w where id=@id
   return @w
end

select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd

drop function dbo.fn_my

Show quoteHide quote

"John" <topgu***@hotmail.com> wrote in message
news:4434d6af$0$670$fa0fcedb@news.zen.co.uk...
>
> Can I get a SELECT statement to return a comma seperated value list
>
> eg
> I have a table :
>      ID ACT1
>      1 Green
>      1 Blue
>      2 Green
>      2 Red
>      3 Red
>      4 Black
>      5 Green
>
>
> Select ACT1 FROM tbl1 WHERE Id=1
>
> I want to return Green, Blue
>
> then link this to another table and as part of my result set show the
> comma seperated values
>

6 Apr 2006 10:04 AM

John

Thanks for you advise/help

John

Show quoteHide quote

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:O65iGsVWGHA.3624@TK2MSFTNGP04.phx.gbl...
> John
> Do such reports on the client side. In T-SQL it is not reliable .
>
> create table w
> (
> id int not null,
> t varchar(50) not null
> )
>
> insert into w values (1,'abc')
> insert into w values (1,'def')
> insert into w values (1,'ghi')
> insert into w values (2,'ABC')
> insert into w values (2,'DEF')
> select * from w
>
>
> create function dbo.fn_my ( @id int)
> returns varchar(100)
> as
> begin
> declare @w varchar(100)
>   set @w=''
>   select @w=@w+t+',' from w where id=@id
>   return @w
> end
>
> select id,
> dbo.fn_my (dd.id)
> from
> (
> select distinct id from w
> )
> as dd
>
> drop function dbo.fn_my
>
>
>
>
> "John" <topgu***@hotmail.com> wrote in message
> news:4434d6af$0$670$fa0fcedb@news.zen.co.uk...
>>
>> Can I get a SELECT statement to return a comma seperated value list
>>
>> eg
>> I have a table :
>>      ID ACT1
>>      1 Green
>>      1 Blue
>>      2 Green
>>      2 Red
>>      3 Red
>>      4 Black
>>      5 Green
>>
>>
>> Select ACT1 FROM tbl1 WHERE Id=1
>>
>> I want to return Green, Blue
>>
>> then link this to another table and as part of my result set show the
>> comma seperated values
>>
>
>

6 Apr 2006 10:36 AM

amish

In SQL Server 2005 you can use

select id , (select act1 + ',' as [text()]
from tbl tbl1 where tbl1.id = tbl.id order by id for xml path(''))
from tbl
group by id

Regards
Amish Shah

Thread options