>Hi
>
>I've got a member table, a contact table and an organisation table.
>
>
>A member is part of an organisation via a linking contact table and should only be a member of one organisation.
>
>I want to list members who are members of more than one organisation and on the same line have the organisation code.
>
>So
>
>member001,ORG01,ORG02
>member002,ORG01,ORG06,ORG08
>
>etc
>
>Any thoughts on the best way to produce this would be welcome.
From the top of my head:
;with cteMembers as (select M.MemberID, O.OrgID, count(O.OrgID) over (partition by M.MemberID) as cntOrganizations
from Members M inner join Contacts C ON M.MemberID = C.MemberID inner join Organization O on C.OrgID = O.OrgID)
select cm.MemberID, stuff((select ',' + cm1.OrgID from cteMembers cm1
where cm1.MemberID = cm.MemberID and cm1.cntOrganizations > 1
order by OrgID for XML PATH('')),1,1,'') as OrgIDs from cteMembers cm
where cm.cntOrganizations > 1
GROUP BY cm.MemberID
ORDER BY cm.MemberID
If it's not broken, fix it until it is.
My Blog