How can i use stuff function for multiple columns in SQL server?

Question

I have a requirement for concatenating two values of two rows having same Id's and averaging for other column. Here is the sample table I have:

Now my requirement is I need to concatenate the Response column, concatenate Response Rating column and average the Rating Avg column if it has same ParticipantId, UseriD, QuestionId and ConductedById.

Here is the target data what I wanted:

Here Response column and Response rating column is concatenated with respective rows and Rating Avg column is taken the average. I have done one column concatenation previously using stuff function. Can this be achieved using stuff function?

Answer 1

You can do the following. Just group by those columns and make 2 subselects for concatenated columns:

select UserID, 
       ConductedByID, 
       QuestionID, 

       (SELECT STUFF((SELECT ';' + Response
                      FROM TableName tn2 WHERE tn1.UserID = tn2.UserID and 
                                     tn1.ConductedByID = tn2.ConductedByID and
                                     tn1.QuestionID = tn2.QuestionID and 
                                     tn1.ParticipantID = tn2.ParticipantID
            FOR XML PATH('')) ,1,1,'')) as Response,

       (SELECT STUFF((SELECT ';' + cast(Rating as varchar)
                      FROM TableName tn2 WHERE tn1.UserID = tn2.UserID and 
                                     tn1.ConductedByID = tn2.ConductedByID and
                                     tn1.QuestionID = tn2.QuestionID and 
                                     tn1.ParticipantID = tn2.ParticipantID
            FOR XML PATH('')) ,1,1,'')) as [Response Rating],

       AVG(case when Rating = 'n/a' then 0 else cast(Rating as int) end) as [Rating Avg], 
       ParticipantID
from TableName tn1
group by UserID, ConductedByID, QuestionID, ParticipantID