如何在 MSSQL 中合并多行 [英] How to combine multiple rows in MSSQL

问题描述

可能的重复:
在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

这篇关于如何在 MSSQL 中合并多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！