Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
673 views
in Technique[技术] by (71.8m points)

sql server - 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  |

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

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.

Any help with be very much appreciated!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

Table

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

Data

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'

Query

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]

Result

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...