Comma separated values from the database end..

scenario 1. Type table Contains.. Type ID, Type Name Category table Contains.. Category ID, Category Name TypeCategory Table Contains...Type ID and Category ID.. as composite Primary key.
scenario 2. Show the Type Names with associated comma separated Category Names..
Solution: 1
SELECT RelationshipTypeId,RelationshipTypeName, RelationshipCategoryList = STUFF( ( SELECT ', ' + RelationshipCategoryName FROM ( select rt.RelationshipTypeName,rc.RelationshipCategoryName from Directory.RelationshipTypeCategory rtc inner join Directory.RelationshipType rt on rt.RelationshipTypeId = rtc.RelationshipTypeId inner join Directory.RelationshipCategory rc on rc.RelationshipCategoryId = rtc.RelationshipCategoryId ) b WHERE b.RelationshipTypeName = a.RelationshipTypeName FOR XML PATH('') ) , 1, 2, '') FROM ( select rtc.RelationshipTypeId,rt.RelationshipTypeName,rc.RelationshipCategoryName from Directory.RelationshipTypeCategory rtc inner join Directory.RelationshipType rt on rt.RelationshipTypeId = rtc.RelationshipTypeId inner join Directory.RelationshipCategory rc on rc.RelationshipCategoryId = rtc.RelationshipCategoryId ) a GROUP BY RelationshipTypeName,RelationshipTypeId
Solution 2:
select distinct RelationshipTypeId, (SELECT SUBSTRING((SELECT ',' + cast(s.RelationshipCategoryId as varchar) FROM Directory.RelationshipTypeCategory s where x.RelationshipTypeId=s.RelationshipTypeId ORDER BY s.RelationshipCategoryId FOR XML PATH('')),2,200000) AS CSV) AS CategoryIds from Directory.RelationshipTypeCategory x