如何在 MSSQL 中合并多行

2022-01-22 00:00:00 pivot sql sql-server

可能的重复:
在MS SQL Server 2005中模拟group_concat MySQL函数?
SQL Server 中的连接组

我正在尝试编写一个将行组合在一起的 SQL 查询.我需要它按 ID# 将未指定数量的行组合在一起，但将它们的地址连接到一个单元格中.

I am trying to write an SQL query that will combine rows together. I need it to group together an unspecified number of rows by ID# but concatenate their addresses let's say into one cell.

说我们有

ID, Address
p1 a1
p1 a2
p1 a3
p2 a4
p2 a5

我想得到

ID, Address
p1 a1,a2,a3
p2 a4,a5

每个 ID 的地址数量可能会有所不同.有些 ID 为 1，有些则为 50.

The number of addresses per ID can vary. Some IDs have 1, others can have 50.

任何帮助将不胜感激.提前致谢！

Any help would be appreciated. Thanks in advance!

推荐答案

Select T1.Id
    , Stuff(
        (
        Select ', ' + T2.Address
        From MyTable As T2
        Where T2.Id = T1.Id
        Order By T2.Address
        For Xml Path(''), type
        ).value('.', 'nvarchar(max)'), 1, 2, '') As Address
From MyTable As T1
Group By T1.Id

相关文章