Merge multiple rows into a single row

Question

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!

Please select a answer as the right answer when you get a chance - it will help you get quicker / better answers in the future... okay, not really, but we like to say that. — SQLMason, May 11 '12 at 17:34

SQLMason · Answer 1 · 2012-05-11T14:39:16.447

7

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

edited May 11 '12 at 14:39

answered May 11 '12 at 14:32

SQLMason

3,275
1
30
40

Dan Im afraid I cant seem to get that to work with my existing code. I must add that Im using Sql Management Studio to devlop this as im not SQL trained. – Raddo2003 May 11 '12 at 21:49
Any other suggestions or advice? – Raddo2003 May 11 '12 at 21:50
Managed to convert the above to my tables, but the error im getting is "Conversion failed when converting the varchar value ', ' to data type int". THis must be because I already have a lookup apllied from my original view. – Raddo2003 May 11 '12 at 22:35
Sorry, I would have to see all of the data to figure out what you did. I wrote the example in SSMS as well - it's what we all use :) Are you inserting your data into my variable table? If so... don't. Just change the "FROM @table" to your table in your database in the query (two places). – SQLMason May 12 '12 at 00:56
Dan, thanks for your help so far. I will try and break this down for you a bit more: – Raddo2003 May 14 '12 at 10:20
Type_ID | Client_ID | Code_ID |Description | 127 | 090820006311404926326C | 10 |Aastra | 127 | 090820006311404926326C | 8 |Ericsson | 127 | 111012237401404926326C | 10 |Aastra | 127 | 120209287521404926326C | 10 |Aastra | 127 | 120209287521404926326C | 7 |Alcatel | – Raddo2003 May 14 '12 at 10:24
Code_ID contains the INT reference that is then looked up against another table for the name. SO i need to run the query against the original table and then run a look up so it doesnt just bunch 8, 6 together. Not that it works anyway as the error im getting is Conversion failed when converting the varchar value ',' to data type int. – Raddo2003 May 14 '12 at 10:26
Sounds like you have a problem with your "lookup" and it has nothing to do with your question. "Don't scope creep on me, bro!" :) Did you change the `@table` to your view / table? – SQLMason May 14 '12 at 12:31
Yes, still got the error. I will try removing the lookup first. – Raddo2003 May 14 '12 at 15:12

score 0 · Answer 2 · edited May 28 '12 at 13:15

Dan, I have managed to get this working using your original Ideas with some modifications. Although I cannot save it as a view as I understand that you cannot save DECLARES as VIEWS;

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

INSERT  INTO @table
        SELECT  dbo.AMGR_User_Fields_Tbl.Type_Id, dbo.AMGR_User_Fields_Tbl.Client_Id, dbo.AMGR_User_Field_Defs_Tbl.Description AS PBX_Vendor
            FROM    dbo.AMGR_User_Fields_Tbl INNER JOIN
                    dbo.AMGR_User_Field_Defs_Tbl ON dbo.AMGR_User_Fields_Tbl.Type_Id = dbo.AMGR_User_Field_Defs_Tbl.Type_Id AND 
                    dbo.AMGR_User_Fields_Tbl.Code_Id = dbo.AMGR_User_Field_Defs_Tbl.Code_Id
            WHERE     (dbo.AMGR_User_Fields_Tbl.Type_Id = 127)

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]

don't forget to mark my answer as the selected answer (2 years later) — SQLMason, May 01 '14 at 02:30
don't forget to mark my answer as the selected answer (5 years later) — SQLMason, Feb 07 '17 at 15:59

Merge multiple rows into a single row

2 Answers2

Linked