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

问题描述

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

Possible Duplicates:
Simulating group_concat MySQL function in MS SQL Server 2005?
Concat groups in 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个，其他ID可以有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屋！