column data in the single row

Hi,
I have a table such as

ID Name OS
-----------
10 Paul AIX
10 Paul SOLARIS
10 Paul NT
20 Jack NT
20 Jack SOLARIS

and I have asked to create an output as

ID NAME OS
-----------
10 Paul AIX,SOLARIS,NT
20 JAck NT,SOLARIS

How can I get this output via sql.
Also a good source for such tricky SQLs would be very fruity.Hi Faar,

If this is a one-time deal for a report, then I would go ahead and plug the dreaded cursor within a cursor. If your example table is named testing and is defined as such:

create table testing
(
ID int,
Name varchar(30),
OS varchar(100)
)

-and your values are as you provided. Then the code below should work:

declare @.id int,
@.name varchar(30),
@.OS varchar(100),
@.CurrentOS varchar(100)

create table #formatted
(
ID int,
Name varchar(30),
OS varchar(100)
)

declare person cursor for
select id, name from testing
group by id, name

open person

fetch person into @.id, @.name
while @.@.fetch_status = 0
begin
set @.OS = ''
declare OS cursor for
Select OS from testing
where ID = @.ID
group by OS

open OS
fetch OS into @.CurrentOS

while @.@.fetch_status = 0
begin
set @.OS = @.OS + @.CurrentOS + ', '
fetch OS into @.CurrentOS
end
Set @.OS = Left(@.OS,LEN(@.OS)-1)
close OS
deallocate OS

insert #formatted (id, name, os)
values (@.id, @.name, @.OS)

fetch person into @.id, @.name
end
close person
deallocate person

select * from #formatted

drop table #formatted

--This is pretty much textbook for bad sql - but if you only need to to this once I wouldn't worry about it. If you need to do this regularly, there are better performing methods than the cursors such as cycling through a table variable.

good luck.|||Warning! Untested code. May have syntax errors...
create function OSList(@.ID integer)
returns varchar(500)
as
begin
declare @.ReturnValue varchar(500)
select @.ReturnValue = isnull(@.ReturnValue + ', ', '') + OS
from [YourTable]
where ID = @.ID
order by OS
Return @.ReturnValue
end

To execute:select distinct
ID,
Name,
dbo.OSList(ID)
from [YourTable]|||hi,
"create function " suggestion works very well.
thanks everybody.

Do you know a good source for such tricky SQLs?|||Just search any of the SQL Server forum like :
SQLTeam.com
SQLServerCentral.com
SQL-Server-Performance.com
forums.microsoft.com|||Celko has written good books on SQL.