SQL Server 2014 Insert Stuff function

Question

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam


SubjectID       StudentName
----------      -------------
1               Mary, John, Sam

Hi, i'm using SQL Server 2014. I would like to know if it's possible to use the STUFF() function to insert the result into one column. The examples i see online are all retrieving. I tried to do it based on the documentation by it doesn't seems to be correct.

Query

 @"INSERT INTO ApplicationOtherInfo 
                        (ApplicationId, AppOptionCode
                         ) values 
                        (@applicationId, @appCode
                         )";

SELECT STUFF((SELECT ',' + AppOptionCode
              FROM ApplicationOtherInfo 
              ORDER BY AppOptionCode
              FOR XML PATH('')), 1, 1, '')

Yes it is: https://msdn.microsoft.com/en-us/library/ms188043.aspx — rbr94, Sep 23 '16 at 07:29

score 0 · Answer 1 · answered Sep 23 '16 at 07:44

Yes you can:

DECLARE @Students TABLE (
    SubjectID int,
    StudentName nvarchar(max)
)
INSERT INTO @Students VALUES
(1, 'Mary'),
(1, 'John'),
(1, 'Sam')

INSERT INTO SomeTable
SELECT DISTINCT 
        s.SubjectID,
        STUFF((SELECT ', ' + StudentName
              FROM @Students 
              WHERE s.SubjectID = SubjectID
              FOR XML PATH('')), 1, 2, '') 
                        as StudentName
FROM @Students s

If you select from SomeTable you will get:

SubjectID   StudentName
1           Mary, John, Sam

SQL Server 2014 Insert Stuff function

1 Answers1