saTech saTech - 5 months ago 7
SQL Question

Rolling up multiple rows in single row

I am trying to merge rows of employee DB table

Here is my original table

enter image description here

I want to merge rows based on department. here is my expected result.

enter image description here

I tried using FOR XML PATH('')), 1, 1, '') but I canroll up only one column.

I know we have similar question here but its rolling up only one column.

any help is much appreciated

Answer

Just use the same method for the other columns:

SELECT
    t.Department,
    Worker = 
        STUFF((
            SELECT ';' + Worker
            FROM tbl 
            WHERE Department = t.Department
            ORDER BY Worker
            FOR XML PATH(''), TYPE
        ).value('text()[1]','NVARCHAR(MAX)'), 1, 1, N''),
    Phone = 
        STUFF((
            SELECT ';' + Phone
            FROM tbl 
            WHERE Department = t.Department
            ORDER BY Worker
            FOR XML PATH(''), TYPE
        ).value('text()[1]','NVARCHAR(MAX)'), 1, 1, N''),
    Ext = 
        STUFF((
            SELECT ';' + Ext
            FROM tbl 
            WHERE Department = t.Department
            ORDER BY Worker
            FOR XML PATH(''), TYPE
        ).value('text()[1]','NVARCHAR(MAX)'), 1, 1, N'')
FROM tbl t
GROUP BY t.Department
Comments