英文:
Include convert multiple rows into one with comma as separator in main view Sql
问题
Tables diagram
我有两个表，主表和子表。
我需要在一个逗号分隔的字符串中包含每个服务的所有用户。
SELECT SrvID, SrvTitle, SrvStatus, '' AS SrvUsersText
FROM ServicesData
要达到如下结果
英文:
Tables diagram
I had two tables main and sub table.
I need to include all users for each service in one comma delimited string
SELECT SrvID, SrvTitle, SrvStatus, '' AS SrvUsersText
FROM ServicesData
To be result like this
答案1
得分: 0
如@Luuk评论所述，要将列表以逗号分隔的形式获取，您需要使用STRING_AGG函数，像这样：
SELECT SrvID, SrvTitle, SrvStatus,
STRING_AGG(concat(su.LineUserId,'-',su.LineUserName), ',') AS SrvUsersText
FROM ServicesData
Join ServiceUsers su on <conditions>
group by SrvID, SrvTitle, SrvStatus
英文:
As @Luuk commented, for getting list as comma separated you need to use STRING_AGG function like this:
SELECT SrvID, SrvTitle, SrvStatus,
STRING_AGG(concat(su.LineUserId,'-',su.LineUserName), ',') AS SrvUsersText
FROM ServicesData
Join ServiceUsers su on <conditions>
group by SrvID, SrvTitle, SrvStatus
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论