SQL Server 2012/2014列值合并问题：替代STRING_AGG实现行转列

我看了你遇到的问题——在不支持STRING_AGG的SQL Server版本里，用STUFF+FOR XML PATH合并列值时出现了XML转义字符和重复行，这是因为几个常见的小问题没处理好，咱们一步步来修正：

问题根源分析 #

1. XML标签与转义问题：你原来的子查询select a.peso for xml path('')会生成带XML标签的内容（比如<peso>1</peso>），转换为字符串时，<>会被自动转义成<>，就出现了你看到的<columns5>这类内容。
2. GROUP BY错误导致重复行：外层查询的GROUP BY包含了a.peso、a.titolo这些需要聚合的字段，这会让每个不同的peso/titolo都单独生成一行，根本没起到合并的作用。
3. STUFF参数使用不当：你用STUFF(..., 1, 0, ',')是在结果开头加逗号，而不是去掉多余的前缀，逻辑搞反了。

修正后的查询语句 #

下面是调整后的完整查询，我会标注关键修改点：

select 
    w1.idQuestionario, 
    w1.nominativo, 
    w1.media, 
    w1.valutazione, 
    count(w1.risposto) as Funzionari,
    -- 修正：用type+value避免XML转义，STUFF去掉开头的逗号
    STUFF(
        (select ',' + cast(a.peso as varchar(10)) 
         from (
             select r.peso, u.nominativo
             from Domanda d 
             join Questionario as q ON q.idQuestionario = d.idQuestionario 
             join Risposta as r ON r.idDomanda = d.idDomanda 
             join rUtenteRisposta as ur on ur.idRisposta = r.idRisposta 
             join utente u ON u.matricola = ur.matricola 
             where q.idQuestionario = '111222' and q.cancellato = 0 and q.anonimo = 0
         ) a 
         where a.nominativo = w1.nominativo -- 关联主查询的分组键，确保只合并当前行的数据
         for xml path(''), type
    ).value('.', 'nvarchar(max)'), 1, 1, '') as peso,
    -- 同样的逻辑处理titolo列
    STUFF(
        (select ', ' + a.titolo 
         from (
             select d.titolo, u.nominativo
             from Domanda d 
             join Questionario as q ON q.idQuestionario = d.idQuestionario 
             join Risposta as r ON r.idDomanda = d.idDomanda 
             join rUtenteRisposta as ur on ur.idRisposta = r.idRisposta 
             join utente u ON u.matricola = ur.matricola 
             where q.idQuestionario = '111222' and q.cancellato = 0 and q.anonimo = 0
         ) a 
         where a.nominativo = w1.nominativo
         for xml path(''), type
    ).value('.', 'nvarchar(max)'), 1, 2, '') as titolo
from ( 
    select 
        w.nominativo, 
        w.idQuestionario, 
        w.risposto, 
        sum(w.valore) / convert(float, count(w.Domande)) as media, 
        w.valutazione 
    from ( 
        select 
            u.nominativo, 
            q.idQuestionario, 
            q.nome, 
            d.idDomanda as Domande, 
            r.peso , 
            ur.matricola as risposto, 
            1 * r.peso as valore, 
            sum(us.valutazione) / convert(float, count(us.idSezione)) as valutazione 
        from Questionario q 
        join Domanda d ON d.idQuestionario = q.idQuestionario 
        join Risposta r ON r.idDomanda = d.idDomanda 
        join rUtenteRisposta ur ON ur.idRisposta = r.idRisposta 
        join Utente u ON u.matricola = ur.matricola 
        left join rUtenteSezione us ON us.idQuestionario = q.idQuestionario AND us.matricola = u.matricola 
        where q.cancellato = 0 and q.idQuestionario = '111222' and q.anonimo = 0 
        group by u.nominativo, q.idQuestionario, q.nome, d.idDomanda, r.peso, ur.matricola 
    ) w 
    group by w.idQuestionario,w.risposto,w.nominativo,w.valutazione 
) w1
group by w1.idQuestionario, w1.media, w1.nominativo, w1.valutazione -- 只保留需要分组的核心字段
order by w1.nominativo -- 可以根据你的需求调整排序字段

关键修改说明 #

1. 避免XML转义：在子查询后添加, type，再用.value('.', 'nvarchar(max)')提取纯文本，这样就不会出现<>转义的问题了。
2. 正确合并值：子查询里用',' + 字段的方式给每个值加前缀，然后用STUFF(..., 1, 1, '')去掉第一个多余的逗号（如果是, 就去掉前2个字符，比如titolo列的处理）。
3. 关联分组键：子查询通过where a.nominativo = w1.nominativo关联主查询的分组字段，确保每个分组只合并对应的数据，不会交叉聚合。
4. 修正GROUP BY：外层GROUP BY只保留w1的核心分组字段，去掉原来的a.peso、a.titolo，这样就能把同一分组的多行值合并成一行。

这个查询应该能输出你期望的结果：

column1 column2 column3 column4 column5 column6
aaaaa bbbbb 0,2 6 1,2 how are you?, did you eat?
ccccc dddddd 0,5 7 1,1 how are you?, did you eat?

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