Merge multiple rows into a single row

by

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

More Related Contents:

  1. Export from SQL Server to Excel
  2. How to get cumulative sum
  3. How to check if a column exists in a SQL Server table
  4. Get first day of week in SQL Server
  5. SQL Server query to find all permissions/access for all users in a database
  6. Infinite loop CTE with OPTION (maxrecursion 0)
  7. How Can I Log and Find the Most Expensive Queries?
  8. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  9. SQL Server add auto increment primary key to existing table
  10. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  11. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  12. INSERT INTO vs SELECT INTO
  13. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  14. Do while loop in SQL Server 2008
  15. TSQL: How to convert local time to UTC? (SQL Server 2008)
  16. How to: Create trigger for auto update modified date with SQL Server 2008
  17. How to update primary key
  18. error when inserting into table having instead of trigger from entity data framework
  19. Case sensitive variables in SQL Server
  20. Transpose rows and columns with no aggregate
  21. What happens to the primary key Id when it goes over the limit?
  22. SQL Recursive CTE: Finding objects linked by property
  23. Convert decimal time to hours and minutes
  24. How do I move a table into a schema in T-SQL
  25. nvarchar(max) vs NText
  26. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  27. How to find the boundaries of groups of contiguous sequential numbers?
  28. How to wait for 2 seconds?
  29. Find broken objects in SQL Server
  30. Populate Dataset With Table Names From Stored Procedure

Leave a Comment