how to split ID's into separate groups based on what they have

https://stackoverflow.com/q/78625551/1690217Shows how to construct a CSV group value

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE Party (
  Partyid varchar(10),
  Name VARCHAR(100),
  FruitCode Varchar(50),
  Description VARCHAR(255)
);

INSERT INTO Party(PartyID, Name, FruitCode, Description) VALUES 
('123','John', 'A', 'Apple'),
('456','Jane', 'O', 'Orange'),
('789', 'Jack', 'A', 'Apple'),
('789', 'Jack', 'O', 'Orange'),
('012', 'Jill', 'O', 'Orange'),
('123', 'John', 'O', 'Orange');
/*
-- Using Simple STRING_AGG doesn't always work
SELECT [PartyID], STRING_AGG([FruitCode],',') as FruitCodes
FROM Party
GROUP BY PartyID;
*/

-- Apply a FOR XML PATH lookup into the dataset
SELECT [PartyID], [Name], [FruitCode], [Description], [FruitCodes].[Codes] as [GroupCode]    
FROM [Party] 
CROSS APPLY
    (
        SELECT [FruitCode] + ','
        FROM [Party] AS [lookup] 
        WHERE [lookup].[PartyID] = [Party].[PartyID]
        ORDER BY [FruitCode] DESC
        FOR XML PATH('')
    ) FruitCodes ([Codes]);
    
-- To Group just the people we can do this
SELECT [PartyID], [Name], [FruitCodes].[Codes]  
FROM [Party] 
CROSS APPLY
    (
        SELECT [FruitCode] + ','
        FROM [Party] AS [lookup] 
        WHERE [lookup].[PartyID] = [Party].[PartyID]
        ORDER BY [FruitCode] DESC
        FOR XML PATH('')
    ) FruitCodes ([Codes])
GROUP BY [PartyID], [Name], [FruitCodes].[Codes];

-- If you want to see the Fruits they have we can still use STRING_AGG
SELECT [PartyID], [Name], [FruitCodes].[Codes] as [GroupCode], STRING_AGG([Description],', ') as Fruit  
FROM [Party] 
CROSS APPLY
    (
        SELECT [FruitCode] + ','
        FROM [Party] AS [lookup] 
        WHERE [lookup].[PartyID] = [Party].[PartyID]
        ORDER BY [FruitCode] DESC
        FOR XML PATH('')
    ) FruitCodes ([Codes])
GROUP BY [PartyID], [Name], [FruitCodes].[Codes];

-- alternatively you could have used the description in the grouping code, for a lot of fruit, this might be a very long value!
-- here we can clean up the last comma too and change the order of the fruit
SELECT [PartyID], [Name], LEFT([FruitCodes].[Codes], LEN([FruitCodes].[Codes]) - 1) as [Fruit]
FROM [Party] 
CROSS APPLY
    (
        SELECT [Description] + ', '
        FROM [Party] AS [lookup] 
        WHERE [lookup].[PartyID] = [Party].[PartyID]
        ORDER BY [FruitCode]
        FOR XML PATH('')
    ) FruitCodes ([Codes])
GROUP BY [PartyID], [Name], [FruitCodes].[Codes];

PartyID Name FruitCode Description GroupCode
---------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123 John A Apple O,A,
456 Jane O Orange O,
789 Jack A Apple O,A,
789 Jack O Orange O,A,
012 Jill O Orange O,
123 John O Orange O,A,
PartyID Name Codes
---------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
012 Jill O,
123 John O,A,
456 Jane O,
789 Jack O,A,
PartyID Name GroupCode Fruit
---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
012 Jill O, Orange
123 John O,A, Orange, Apple
456 Jane O, Orange
789 Jack O,A, Apple, Orange
PartyID Name Fruit
---------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
012 Jill Orange
123 John Apple, Orange
456 Jane Orange
789 Jack Apple, Orange