如何在 sql server 中使用 group_concat 进行查询

2022-01-31 00:00:00 sql-server string-aggregation sql-server-group-concat

我知道在 sql server 中我们不能使用 Group_concat 函数，但这是我遇到的一个问题，我需要 Group_Concat 我的查询.我在谷歌上找到了一些逻辑但无法纠正.我的 sql 查询是

I know that in sql server we cannot use Group_concat function but here is one issue i have in which i need to Group_Concat my query.I google it found some logic but not able to correct it.My sql query is

select  m.maskid,m.maskname,m.schoolid,s.schoolname,
md.maskdetail
from tblmask m join school s on s.id = m.schoolid 
join maskdetails md on m.maskid = md.maskid
order by m.maskname ;

它给了我这样的结果

只需查看前 3 行，其中 maskid、maskname、schoolid、schoolname 相同，但 maskdetail 不同，因此想要一行，其中最后一列可以包含根据 maskid 的所有 maskdetail 等等.

Just look first 3 rows In that maskid,maskname,schoolid,schoolname is same but maskdetail is different so want to one row for that in which last column can contain all maskdetails as per maskid and so on.

我想要我的输出

等等.所以请在查询时帮助我.

And so on.So please help me while making a query for that.

提前致谢.

推荐答案

查询:

SELECT
      m.maskid
    , m.maskname
    , m.schoolid
    , s.schoolname
    , maskdetail = STUFF((
          SELECT ',' + md.maskdetail
          FROM dbo.maskdetails md
          WHERE m.maskid = md.maskid
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.tblmask m
JOIN dbo.school s ON s.ID = m.schoolid
ORDER BY m.maskname

其他信息:

世界中的字符串聚合SQL Server

相关文章