ugg...i was pasting an example that uses the FOR XML to resolve this, and realized you posted in a SQL 2000 forum.
the best way is to use the trick that the FOR XML command can do for you; even for large lists, is pretty efficient.
i'm pretty sure it still works, i changed my 2005 database to compatibility level 80, and it worked just fine, but you'll need to test it:
here's typical results with the values i used...i was too lazy to change every data element to your examples.
declare @skills table (Resource_Id varchar(30), Skill_Id varchar(20))
insert into @skills
select 'Email1' , 'sqlserver' union all
select 'Email1' , 'vb.net' union all
select 'Email1' , 'oracle' union all
select 'Email2', 'sqlserver' union all
select 'Email2', 'java' union all
select 'Email2', 'excel' union all
select 'Email3', 'vb.net' union all
select 'Email3', 'java' union all
select 'Email3', 'oracle'
select * from @skills s1
--- Concatenated Format
set statistics time on;
SELECT Resource_Id,stuff(( SELECT ',' + Skill_Id
FROM @skills s2
WHERE s2.Resource_Id= s1.resource_ID --- must match GROUP BY below
ORDER BY Skill_Id
FOR XML PATH('')
),1,1,'') as [Skills]
FROM @skills s1
GROUP BY s1.Resource_Id --- without GROUP BY multiple rows are returned
ORDER BY s1.Resource_Id
set statistics time off;
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!