使用重复数据提交的SQL合并

Question

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

我的数据目前如下：

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路径，但仍然有重复的行。

请帮我解决这个问题，谢谢。

Answer 1

在TSQL中，您可以使用STRING_AGG((

select  STRING_AGG(concat(job1,'-', Unit1_level1, '-', Unit1_level2, ':'))  
from your_table 

Answer 2

尝试此组合STRING_AGG和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

Answer 3

您可以使用STRING_AGG()和CONCAT_WS()如下：

SELECT Name,
STRING_AGG(CONCAT_WS('-', job1, Unit1_Level1, Unit1__Level2), ':')
FROM YourTable
GROUP BY Name;

Answer 4

String_Agg需要一个分隔符值

select  STRING_AGG(concat(job1,'-', Unit1_Level1, '-', Unit1_Level2, ':'),' ') a from @tmp