May 26, 2018 at 9:43 am
Can't believe the problem I'm having with what should be a simple problem. I'm trying to
concatenate some columns with a one to many relationship.
Here is my sample schema:
CREATE TABLE Member
(Memberid int, Name varchar(50))
CREATE Table Program
(ProgramID int, ProgramName varchar(50))
Create table MemberProgram
(MemberID int, ProgramID int, IsPrimary bit)
INSERT INTO Member
([MemberID], [Name])
VALUES
(1, 'Jim'),
(2, 'Bob'),
(3, 'Todd')
INSERT INTO Program
([ProgramID], [ProgramName])
VALUES
(1, 'CHF'),
(2, 'Diabetes'),
(3, 'CKD'),
(4, 'Hypertension') Insert into MemberProgram
(MemberID , ProgramID, IsPrimary)
Values
(1,1,1),
(2,2,1),
(3,3,1),
(1,4,0),
(2,1,0),
(1,3,0)
I trying to get a result like this with the primary group listed first:
MemberID Programs
1 CHF, Hypertension, CKD
2 Diabetes, CHF
3 CKD
I'm thinking I need a recursive CTE, but can't seem to get a handle on the query.
Any help would be greatly appreciated.
Todd
May 26, 2018 at 11:05 am
Give this a twirl
with cte
as (
select a.memberid,b.ProgramID,c.ProgramName
from Member a
join MemberProgram b on a.Memberid=b.MemberID
join Program c on c.ProgramID=b.ProgramID
)
select a.memberid,stuff(( select ','+Programname
from cte d
where d.Memberid=a.Memberid
for xml path ('')),1,1,'')
from cte a
group by a.memberid
***The first step is always the hardest *******
May 26, 2018 at 12:41 pm
Works like a champ. Thank you very much for your assistance !!!
Based on your example, I moved the XML part into the CTE like this:
; with mycte as
(
SELECT
a.MemberID,
STUFF(
(SELECT ',' + ProgramName
From Member m
Inner Join MemberProgram mp on m.memberid = mp.MemberID
Inner join Program p on p.programid = mp.programid
WHERE mp.Memberid = a.[Memberid]
Order By m.memberid , isprimary DESC
FOR XML PATH (''))
, 1, 1, '') AS ProgramList
FROM Member AS a
)
select * from mycte
May 26, 2018 at 1:07 pm
can be further simplified; with mycte as
(SELECT a.MemberID
, STUFF(
(SELECT ',' + p.ProgramName
From MemberProgram mp
Inner join Program p on p.programid = mp.programid
WHERE mp.Memberid = a.Memberid
Order By mp.isprimary DESC
FOR XML PATH (''))
, 1, 1, '') AS ProgramList
FROM Member AS a
)
select * from mycte
May 27, 2018 at 6:31 am
Quick suggestion, add the TYPE directive to handle any extended characters and the text() function to prevent the reconstruct of the XML for the output.
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply