Stored procedure to insert concatenated string into table-CodePudding

I have two tables, ParentPayor and ChildPayor.

ParentID is the primary key in ParentPayor and ParentID is a foreign key in the ChildPayor table. The ChildPayor table has a column State.

I would like to create a stored procedure that concatenates each State in the ChildPayor table, and inserts the string into the ParentPayor column States, where ChildPayor.ParentID = ParentPayor.ParentID.

I just discovered STRING_AGG to concatenate:

    STRING_AGG (State, ',') 
FROM ChildPayors AS States 
WHERE ParentPayorID = 32

But I would like to be able to concatenate all States within the ChildPayor, and insert into ParentPayor where the ParentIDs match. Does this make sense?

Something like (I know this is incorrect):

SELECT STRING_AGG (State, ',') 
FROM ChildPayors, ParentPayors AS States 
WHERE ParentPayors.ParentPayorID = ChildPayors.ParentPayorID

INSERT INTO ParentPayors(States) 
VALUES (States)

CodePudding user response：

I will often use CROSS APPLY to calculate intermediate values. Try something like:

DECLARE @ParentPayor TABLE (ParentID INT, ParentName VARCHAR(100), States VARCHAR(1000))
INSERT @ParentPayor
VALUES
    (1, 'AAA', null),
    (2, 'BBB', null),
    (3, 'CCC', null),
    (4, 'DDD', null)

DECLARE @ChildPayor TABLE (ChildID INT, ParentID INT, StateName VARCHAR(100))
INSERT @ChildPayor
VALUES
    (1, 1, 'New York'),
    (2, 2, 'Texas'),
    (3, 2, 'Virginia'),
    (4, 2, 'California'),
    (5, 4, 'Virginia'),
    (6, 4, 'New York')

UPDATE P
SET States = S.States
FROM @ParentPayor P
CROSS APPLY (
    SELECT States = STRING_AGG(C.StateName, ', ') WITHIN GROUP(ORDER BY C.StateName)
    FROM @ChildPayor C
    WHERE C.ParentID = P.ParentID
) S

SELECT *
FROM @ParentPayor P

Output:

ParentID	ParentName	States
1	AAA	New York
2	BBB	California, Texas, Virginia
3	CCC	(null)
4	DDD	New York, Virginia

The UPDATE statement above would then become the body of your stored procedure.

On further reflection, a subselect could also be used. The following JOIN could be used in place of the CROSS APPLY:

LEFT JOIN (
    SELECT C.ParentID, States = STRING_AGG(C.StateName, ', ') WITHIN GROUP(ORDER BY C.StateName)
    FROM @ChildPayor C
    GROUP BY C.ParentID
) S ON S.ParentID = P.ParentID