Append column values to certain records from duplicated records and remove duplicated records

Question

This is the script for create table and some data.

--Students table ---------
CREATE TABLE [Students](
    [ID] [int] NOT NULL,
    [SubjectID] [int] NULL,
    [StudentName] [nvarchar](50) NULL,
    [ConcatTo] [bit] NULL
) ON [PRIMARY]
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (1, 1, N'Mary', 1)
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (2, 1, N'Brown', NULL)
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (3, 2, N'Lily2', NULL)
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (4, 2, N'Michilin2', 1)
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (5, 2, N'Joshua2', NULL)
GO


select *from Students;

SELECT Main.SubjectID, main.Students As "Students" 
FROM
(
    SELECT DISTINCT t2.SubjectID, 
        (
            SELECT t1.StudentName + ' ' AS [text()]
            FROM dbo.Students t1
            WHERE t1.SubjectID = t2.SubjectID
            FOR XML PATH ('')
        ) Students
    FROM dbo.Students t2
) Main

Select From the table will have this

At Most I only knew to select like this but i dun know how to update my students table like this

This is my screenshot of the expected result.

How can I update StudentName Column with "Lily2 Michilin2 Joshua2" where ConcatTo = 1 like my select statement? And then remove Lily2 and Joshua2 row?

Answer 1

You can use a CTE to wrap your existing query and then use it to join back to your original and update it

WITH CTE AS
(
    SELECT ID, Main.SubjectID, Main.Students As Students
    FROM
    (
        SELECT ID = MIN(CASE WHEN t2.ConcatTo IS NOT NULL THEN t2.ID END), t2.SubjectID, 
        (
            SELECT t1.StudentName + ' ' AS [text()]
            FROM  Students t1
            WHERE t1.SubjectID = t2.SubjectID
            FOR XML PATH ('')
        ) Students
        FROM  Students t2
        GROUP BY t2.SubjectID
    ) Main
)
UPDATE  s
SET     StudentName = c.Students
FROM    Students s
        inner join CTE c    ON  s.ID    = c.ID 

for the second part, just delete with ConcatTo is null

DELETE  s
FROM    Students s
WHERE   s.ConcatTo  IS NULL