tsql 分组依据的聚合字符串

发布于 2024-10-14 05:23:54 字数 344 浏览 6 评论 0原文

我有两个表：

名称（id，名称）地址（id，name_id，地址）

我想编写返回我的查询：名称，地址列表（地址1，地址2，地址3，..），

例如：

Select A.name, B.list_of_addresses
From Names A
    Inner Join (Select name_id, /*list_of_addresses with comma between them*/
                From Addresses
                Group By name_id)  B ON A.id=B.name_id

原文

I have two tables:

Names(id, name)
Addresses(id, name_id, address)

I want to write query that return me:
name, address list (address1, address2, adress3, ..)

Something like:

Select A.name, B.list_of_addresses
From Names A
    Inner Join (Select name_id, /*list_of_addresses with comma between them*/
                From Addresses
                Group By name_id)  B ON A.id=B.name_id

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一张白纸 2024-10-21 05:23:54

从 SQL Server 2005 开始，您可以使用 For XML 作为实现这一目标的技巧。

Select
    A.name,
    stuff((
        select ',' + B.address
        from Addresses B
        WHERE A.id=B.name_id
        for xml path('')),1,1,'')
From Names A

如果您的地址中还没有逗号，那么它效果很好，但即使有，因为您的要求是在它们之间添加逗号..这可能同样“正确”。

You can use For XML as a trick to achieve that from SQL Server 2005 onwards.

Select
    A.name,
    stuff((
        select ',' + B.address
        from Addresses B
        WHERE A.id=B.name_id
        for xml path('')),1,1,'')
From Names A

It works well if you don't already have commas in the address, but even if it did, since your request is to put commas between them.. this is probably just as 'right'.

回复收藏 0 原文

~没有更多了~