如何在SQL server中为多个列使用stuff函数?

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:

我需要连接两个具有相同Id的行的值，并对其他列求平均值。这是我的样本表:

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.

现在我的要求是，我需要连接响应列、连接响应评级列，如果Avg列具有相同的参与者、UseriD、QuestionId和导体，则需要对其进行平均。

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?

这里，响应列和响应评级列与各自的行连接，Avg评级列取平均值。我之前用stuff函数做过一列连接。这能通过使用功能实现吗?

1 个解决方案

#1

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

您可以执行以下操作。只需按这些列进行分组，并对连接列进行2个子选择:

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