SQL STUFF无法正常工作，为什么？ [英] SQL STUFF not working, why?

问题描述

我在Oracle上进行了很多查询，现在我正在使用SQL Server。
我看到了使用类似功能的方式，例如sql server中oracle中的listagg。

I did lot of querys on Oracle, and now I'm working with SQL Server. I saw the way of use similar function like listagg from oracle in sql server (stuff).

    Select
    sqd.id_question,
    STUFF((Select ',' + nm_departament from tb_departament where sqd.id_departament = id_departament for xml path('')),1,1,'') nm_departements
from
    tb_survey_question_departament sqd  

sintax是正确的，但结果不正确。

The sintax its correct, but the result not.

目标例如是前2行，结果为1-RH，巴西利亚（Planta Brasilia）

The goal is have for example for the 2 top rows, the result as 1 - RH, Planta Brasilia

问题出在哪里？

推荐答案

您应该先与众不同的id_question 和内部加入以 tb_departament 像这样

Select
    sqd.id_question,
    STUFF(( 
             SELECT ',' + td.nm_departament 
             from tb_departament td
             INNER JOIN tb_survey_question_departament  sqd1 ON sqd1.id_departament = td.id_departament 
             WHERE  sqd1.id_question = sqd.id_question
             FOR XML PATH('')
         )
         ,1,1,'') AS nm_departements
from
    (
       SELECT DISTINCT sqd.id_question 
       FROM tb_survey_question_departament sqd 
    ) sqd

这篇关于SQL STUFF无法正常工作，为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！