将多行合并为一行 [英] Merge multiple rows into a single row

问题描述

我想知道将以下行中的数据合并到另一个视图中的一行的最佳方法.

I would like to know the best approach to merge data from the following rows into a single row in another view.

这些是当前显示的结果；

These are the results as they are currently displayed;

 Type_ID | Client_ID              | PBX_Vendor |
 127     | 090820006311404926326C | Aastra     |
 127     | 090820006311404926326C | Ericsson   |
 127     | 111012237401404926326C | Aastra     |
 127     | 120209287521404926326C | Aastra     |
 127     | 120209287521404926326C | Alcatel    |

以下是我希望看到的数据；

The following is how I would like to see the data;

 Type_ID | Client_ID              | PBX_Vendor       |
 127     | 090820006311404926326C | Aastra, Ericsson |
 127     | 111012237401404926326C | Aastra           |
 127     | 120209287521404926326C | Aastra, Alcatel  |

基本上，有多个 PBX 供应商与一个客户端 ID 相关联.对于帮助台系统，我需要在一行中显示此内容.

Basically, there are multiple PBX Vendors associated with a Client ID. I need this display in a single row for a helpdesk system.

我已经用 CONCAT 尝试过这个，但我最终得到的只是一行，其中有 100 多个供应商，这些供应商并非特定于 Client_ID.

I have attempted this already with CONCAT, but all I end up with is a single row with over 100 vendors in it that are not specific to a Client_ID.

非常感谢任何帮助！

推荐答案

这是一种的方法(也适用于 2005):

Here's A way to do it (also works with 2005):

表格

DECLARE @table TABLE
    (
      [Type_ID] INT,
      [Client_ID] VARCHAR(50),
      [PBX_Vendor] VARCHAR(50)
    )

数据

INSERT  INTO @table
        SELECT  127,
                '090820006311404926326C',
                'Aastra'
        UNION ALL
        SELECT  127,
                '090820006311404926326C',
                'Ericsson'
        UNION ALL
        SELECT  127,
                '111012237401404926326C',
                'Aastra'
        UNION ALL
        SELECT  127,
                '120209287521404926326C',
                'Aastra'
        UNION ALL
        SELECT  127,
                '120209287521404926326C',
                'Alcatel'

查询

SELECT  [Type_ID],
        [Client_ID],
        (
          SELECT    STUFF((
                            SELECT  ',' + [PBX_Vendor]
                            FROM    @table
                            WHERE   [Client_ID] = tbl.[Client_ID]
                                    AND [Type_ID] = tbl.[Type_ID]
                            GROUP BY [PBX_Vendor]
                            ORDER BY [PBX_Vendor]
                          FOR
                            XML PATH('')
                          ), 1, 1, '')
        ) PBX_Vendor
FROM    @table tbl
GROUP BY [Type_ID],
        [Client_ID]

结果

Type_ID     Client_ID               PBX_Vendor
127         090820006311404926326C  Aastra,Ericsson
127         111012237401404926326C  Aastra
127         120209287521404926326C  Aastra,Alcatel

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