spread query

[Yaron Avior]

Hi,
I have a table as follows:

HostId PurposeId
====== =========
1 3
1 5
1 6
2 1
2 3
I'm looking for a query which will return:

HostId Purposes
====== ========
1 3, 5, 6
2 1, 3

Is it possible?

Thanks in advance.

[oj]

Yaron,

You could try this...

--sql2k+
create function dbo.udf(@h int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
select @s=isnull(@s+',','')+cast(PurposeId as varchar)
from tb
where HostId=@h
return @s
end
go

select HostId, dbo.udf(HostId) as Purposes
from (select distinct HostId
from tb)Derived

Also, we have a solution that would extend more functionalities. Here is one of
the functionalities http://rac4sql.net/onlinehelp.asp?topic=236 that related to
your current issue.

--
-oj
http://www.rac4sql.net
"Yaron Avior" <ya****@mercury.co.il> wrote in message
news:1b**************************@posting.google.c om...

Hi,
I have a table as follows:

HostId PurposeId
====== =========
1 3
1 5
1 6
2 1
2 3
I'm looking for a query which will return:

HostId Purposes
====== ========
1 3, 5, 6
2 1, 3

Is it possible?

Thanks in advance.

[Erland Sommarskog]

oj (no**********@home.com) writes:

Yaron,

You could try this...

--sql2k+
create function dbo.udf(@h int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
select @s=isnull(@s+',','')+cast(PurposeId as varchar)
from tb
where HostId=@h
return @s
end
go

But beware that this relies on behaviour that is not defined. You
may get what you expect, but there is no guarantee. See
http://support.microsoft.com/default.aspx?scid=287515.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp