时间戳

首页 > 解决方案 > SQL 合并归档重复数据

sql - SQL 合并归档重复数据

问题描述

我有一个包含一些重复数据的表，我想将工作和单位字段合并到一个字段中。

我的数据目前如下所示：

name       job1        Unit1_Level1       Unit1__Level2
-------------------------------------------------------
A          Teacher     Infomation      Information_office
A          Staff       Secretary       Secretary_office
A          Staff       Engineer        Engineer_office
B          Teacher     Finacial        Finacial_office
C          Teacher     Engineer        Engineer_office
C          Staff       Library         Library_office
D          Staff       Library         Library_office

我希望它看起来像这样：

name       job
----------------------------------------------------------------------------------------------------------------
A          Teacher-Infomation-Information_office:Staff-Secretary-Secretary_office:Staff-Engineer-Engineer_office
B          Teacher-Finacial-Finacial_office
C          Teacher-Engineer-Engineer_office:Staff-Library-Library_office
D          Staff-Library-Library_office

我尝试了 FOR XML PATH ，但仍然有重复的行。

请帮我解答一下问题，谢谢。

标签： sqlsql-servertsql

解决方案

Try this combination STRING_AGG an CONCAT_WS

SELECT  name, STRING_AGG(a.ct, ':'  )
FROM (
    SELECT name, CONCAT_WS('-', job1, Unit1_level1, Unit1__Level2, ':')   ct 
    FROM MyTable
) a
GROUP BY name

推荐阅读