Combine multiple lines into one line

Question

I would like to know the best approach for combining data from the following rows into one row in another view.

These are the results that are currently being displayed;

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

The following shows 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 several PBX providers associated with a client identifier. I need this screen on one line for support.

I already did this with CONCAT, but all I have is one line with over 100 providers that are not specific to Client_ID.

Any help is much appreciated!

Answer 1

Here's the A execution method (also works since 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 

Answer 2

Dan, I managed to get this work using your original ideas with some changes. Although I cannot save it as a point of view, because 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]