SQL - combine multiple rows with similar username

Question

I have a database table with users who work at several office locations. These Locations are identified by a number and in my table, I have several users appearing on several rows for each office location they work at. What I want is a SQL query which will combine the records and show all the office locations in one field separated by a coma.

This is my original table:

Id    UserName    OfficeNumber
---   ---------   -------------
1     user01      200
2     user02      220
3     user01      290
4     user03      089
5     user02      019

I want my final table to look like below after running the query:

Id    UserName    OfficeNumber
---   ---------   -------------
1     user01      200, 290
2     user02      220, 019
3     user03      089

Any help will be highly appreciated.

Yes, rows 4 and 5 will be deleted, excerpt for the office number which will be consolidated with the office number in the first row in which the username appears in the table. — Munatela, Oct 04 '13 at 12:42

score 2 · Accepted Answer · edited May 23 '17 at 11:57

You can use SQL-Servers XML extensions to concatenate rows to columns:

SELECT  ID,
        UserName,
        OfficeNumber = STUFF((  SELECT  ',' + CAST(OfficeNumber AS VARCHAR(3))
                                FROM    YourTable b
                                WHERE   a.UserName = b.UserName
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    (   SELECT  ID = MIN(ID), UserName
            FROM    YourTable
            GROUP BY UserName
        ) a;

Example on SQL Fiddle

There is another question on SO where someone has asked the intricacies of how this method works, and there are a few answers, so I won't repeat the explanation here.

EDIT

Just seem that you are not taking the minimum ID for each username, but reassigning a new Number as ID, if ID's are to be reassinged based on the lowest original ID for each username then you can use:

SELECT  ID,
        UserName,
        OfficeNumber = STUFF((  SELECT  ',' + CAST(OfficeNumber AS VARCHAR(3))
                                FROM    YourTable b
                                WHERE   a.UserName = b.UserName
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    (   SELECT  ID = ROW_NUMBER() OVER(ORDER BY MIN(ID)), UserName
            FROM    YourTable
            GROUP BY UserName
        ) a;

Example on SQL-Fiddle

Or if it is in alphabetical order you can use

SELECT  ID = ROW_NUMBER() OVER(ORDER BY a.UserName),
        UserName,
        OfficeNumber = STUFF((  SELECT  ',' + CAST(OfficeNumber AS VARCHAR(3))
                                FROM    YourTable b
                                WHERE   a.UserName = b.UserName
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    YourTable a
GROUP BY a.UserName

Example on SQL-Fiddle

score 1 · Answer 2 · answered Oct 04 '13 at 12:43

1

Query:

SQLFIDDLEExample

SELECT ROW_NUMBER()OVER(ORDER BY c1.UserName) AS Id,
       c1.UserName,
    STUFF((SELECT ', ' +x.OfficeNumber
        FROM Table1 x
        WHERE c1.UserName = x.UserName
        FOR XML PATH ('')
    ),1,1,'') as OfficeNumber
FROM Table1 c1
GROUP BY c1.UserName

Result:

| ID | USERNAME | OFFICENUMBER |
|----|----------|--------------|
|  1 |   user01 |     200, 290 |
|  2 |   user02 |     220, 019 |
|  3 |   user03 |          089 |

answered Oct 04 '13 at 12:43

Justin

9,634
6
35
47

I don't think it is an issue in this case since officenumber appears to be a standard numeric format. But just using `FOR XML PATH('')` without using the additional syntax I have (`, TYPE).value('.', NVARCHAR(MAX))`) will cause XML characters to be escaped. e.g. an office number of `<119>` will become `<119>` – GarethD Oct 04 '13 at 13:11

SQL - combine multiple rows with similar username

2 Answers2