在单行中卷起多行 [英] Rolling up multiple rows in single row

问题描述

我正在尝试合并员工数据库表的行

I am trying to merge rows of employee DB table

这是我的原始表格

我想根据部门合并行.这是我的预期结果.

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

我尝试使用 FOR XML PATH('')), 1, 1, '') 但我只能汇总一列.

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.

非常感谢任何帮助

推荐答案

其他列用同样的方法:

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

这篇关于在单行中卷起多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！