如何修改含STUFF函数的SQL查询以实现指定displayname列效果

我编写的SQL查询语句如下：

SELECT 
    [tbl_flw].[question_id], [tbl_flw].[project_id], 
    [tbl_flw].[follow_up_ans], [tbl_flw].[follow_up_ques], 
    [t1].[question] AS current_ques, 
    [t2].[question] AS followUp_ques, 
    displayname = STUFF((SELECT ', ' + [tbl_ques].[question] 
                         FROM [tbl_flw] 
                         LEFT JOIN [tbl_ques] ON [tbl_ques].[question_id] = [tbl_flw].[follow_up_ques] 
                         WHERE [tbl_flw].[follow_up_ques] = [tbl_ques].[question_id] 
                         FOR XML PATH('')), 1, 2, '')
FROM 
    [tbl_flw] 
INNER JOIN 
    [tbl_ques] t1 ON t1.question_id = [tbl_flw].[question_id] 
INNER JOIN 
    [tbl_ques] t2 ON t2.question_id = [tbl_flw].[follow_up_ques] 
WHERE  
    [project_id] = 162

当前查询的displayname列未按预期聚合，期望该列能按question_id分组，将同一组下所有follow_up_ques对应的question内容合并显示。

修改后的SQL语句 #

SELECT 
    [tbl_flw].[question_id], [tbl_flw].[project_id], 
    [tbl_flw].[follow_up_ans], [tbl_flw].[follow_up_ques], 
    [t1].[question] AS current_ques, 
    [t2].[question] AS followUp_ques, 
    displayname = STUFF((SELECT ', ' + sub_ques.[question] 
                         FROM [tbl_flw] sub_flw
                         INNER JOIN [tbl_ques] sub_ques ON sub_ques.question_id = sub_flw.follow_up_ques
                         WHERE sub_flw.question_id = [tbl_flw].[question_id] -- 关联外层主表的question_id，限定聚合范围
                         FOR XML PATH('')), 1, 2, '')
FROM 
    [tbl_flw] 
INNER JOIN 
    [tbl_ques] t1 ON t1.question_id = [tbl_flw].[question_id] 
INNER JOIN 
    [tbl_ques] t2 ON t2.question_id = [tbl_flw].[follow_up_ques] 
WHERE  
    [project_id] = 162

修改说明 #

原查询的子查询未与外层主表tbl_flw建立关联，导致每次都会返回所有符合follow_up_ques = question_id的记录，而非当前行对应分组的数据。通过添加sub_flw.question_id = [tbl_flw].[question_id]关联条件，子查询会仅聚合当前question_id下所有follow_up_ques对应的问题内容，从而得到预期的合并格式。

内容的提问来源于stack exchange，提问作者azi