group by....

id name
12 tom
12 rick
12 mark
15 jack
15 rock
16 kent
16 mint
I have the data in the above format
I want that in the following format
id name
12 tom,rick,mark
15 jack,rock
16 kent,mint
How can i do that?Your best bet (by a long shot) is to do this kind of processing on the client. It can make much better decisions about what it can cope with, and can do so MUCH more efficiently than the server can do it.

With that being said, you can do this kind of processing like:CREATE TABLE #phred (
id INT NOT NULL
, name VARCHAR(20) NOT NULL
)

INSERT INTO #phred
SELECT 12, 'tom'
UNION ALL SELECT 12, 'rick'
UNION ALL SELECT 12, 'mark'
UNION ALL SELECT 15, 'jack'
UNION ALL SELECT 15, 'rock'
UNION ALL SELECT 16, 'kent'
UNION ALL SELECT 16, 'mint'
UNION ALL SELECT 99, 'phred'

SELECT a.id, Min(a.name)
+ Coalesce(', ' + Min(b.name), '')
+ Coalesce(', ' + Min(c.name), '')
FROM #phred AS a
LEFT JOIN #phred AS b
ON (b.id = a.id
AND a.name < b.name)
LEFT JOIN #phred AS c
ON (c.id = a.id
AND b.name < c.name)
GROUP BY a.id

DROP TABLE #phred-PatP|||This worked fine if i have 3 names for one id,What happens if i have 30,40 names against 1 id.
Should i write 30,40 joins in that case?
can anyone help in this...
Thanks.|||Originally posted by bruce_Reid
This worked fine if i have 3 names for one id,What happens if i have 30,40 names against 1 id.
Should i write 30,40 joins in that case?
can anyone help in this...
Thanks.

You have to do this on client side...|||Originally posted by bruce_Reid
This worked fine if i have 3 names for one id,What happens if i have 30,40 names against 1 id.
Should i write 30,40 joins in that case?
can anyone help in this...
Thanks. That's why I started my post with "Your best bet (by a long shot) is to do this kind of processing on the client." This kind of processing is easily handled on a client... It can be done on the server when you must, but it isn't a good idea if you can avoid it.

-PatP|||set nocount on
if object_id('dbo.t2') is not null drop table dbo.t2
create table t2 (id int, name varchar(25))
go
insert t2 values (12, 'tom')
insert t2 values (12, 'rick')
insert t2 values (12, 'mark')
insert t2 values (15, 'jack')
insert t2 values (15, 'rock')
insert t2 values (16, 'kent')
insert t2 values (16, 'mint')
go
declare @.str varchar(8000), @.id int
declare @.tbl table ([id] int, [name] varchar(25))
select @.id = min([id]) from t2
while @.id is not null begin
set @.str = ''
select @.str = @.str + case when @.str = '' then '' else ', ' end + name from t2 where id = @.id
insert @.tbl select @.id, @.str
select @.id = min([id]) from t2 where [id] > @.id
end
select * from @.tbl