Extracting multiple values as comma seperated

Hi all,

This is my table :

WorkstationNo UserID

101 a1

102 a2

103 a3

101 a2

and there are many other fields too. The output I need is something like this

WorsktationNo OccupiedBy

101 a1,a2

102 a2

103 a3

In the similar fashion I would also require to retrieve the values based on the UserID something like this

UserID Workstations

a1 101

a2 101,102

a3 103

Could someone tell me how to write the query for this.

Here ya go

Code Snippet

create table #t (WorkstationNo int, UserID varchar(10) )

insert into #t

select 101, 'a1'

union all select 102, 'a2'

union all select 103, 'a3'

union all select 101, 'a2'

select WorkstationNo,

substring( ( SELECT ', ' + UserID AS [text()]

FROM #t t2

WHERE t1.WorkstationNo = t2.WorkstationNo

FOR XML path(''), elements ), 2, 1000) as OccupiedBy

from #t t1

group by WorkstationNo

select UserID,

substring( ( SELECT ', ' + convert( varchar(10), WorkstationNo ) AS [text()]

FROM #t t2

WHERE t1.UserID = t2.UserID

FOR XML path(''), elements ), 2, 1000) as OccupiedBy

from #t t1

group by UserID

|||

Thanks DaleJ..

worked like a charm. I guess i will take time to understand the code...