Display all specialisation for a member on a single row in sql

See more:

ASP

SQL-Server

I want to display data like this

user_id specialisation
---------- ----------------------------------------
10 Banking, marketing
20 Business law,civil,family


plz any one can help me how can i display different specilisation in a single row


Thanks in advance



Actually i want like this:

My first table like:

user_id specialisation

10 Banking

10 marketing

20 Business law

20 civil

20 family

but when i need to display i want like this


user_id specialisation
---------- ----------------------------------------
10 Banking, marketing
20 Business law,civil,family



i used this code:


SELECT distinct members.name, REPLACE(RTRIM((SELECT CAST([cat_name] AS VARCHAR(MAX)) + ' ' FROM category INNER JOIN specialisation ON category.cat_id=specialisation.cat_id WHERE (member_id = members.member_id) FOR XML PATH (''))),' ',', ') AS cat_name FROM specialisation INNER JOIN members ON specialisation.member_id = members.member_id INNER JOIN city_list ON members.city = city_list.city_id INNER JOIN category ON specialisation.cat_id = category.cat_id INNER JOIN state_list ON members.state = state_list.state_id where qualification_id=1


but i get the result like this:


name cat_name
---------- ----------------------------------------
10 Banking, marketing
20 Business, law,civil,law,family,law

every word is separated by comma but i need like Business law,civil law,family law

please any one can help me

Thanks