SQL Server：联接和追加列

问题描述：

我有一个SQL问题，希望有人可以帮助我或给我一些指导。非常感谢。

I have a SQL question, and hope someone could help me or give me some pointers. Much appreciated.

我有两个表：A和B。

A具有列 ID （这是主键字段）和 NAME

B列具有列 DUPID ， NAME 和 ID 列键

A has columns ID which is the primary key field, and NAME
B has columns DUPID, NAME and ID which is the primary key

A和B之间的关系在B中， DUPID 包含 A.ID 的某些值，并且请求是将不同的 B.NAME 值附加到 A.NAME 根据联接 A.ID = B.DUPID 。用分号分隔。.

The relationship between A and B is in B, the DUPID contains certain values of A.ID, and the request is to append distinct B.NAME value to A.NAME separated by semi-colon based on join A.ID = B.DUPID..

也许我的解释不清楚，这是一个简单的例子。

Maybe my explanation isn't clear, here is a simple example.

A                                  B
ID  NAME                           DUPID      NAME
1   null                            1             John
2   null                            1             John
3   null                            1             Mark
4   null                            3             Luke
5   null                            3             Luke
                                    3             Luke
                                    3             Matthew

因此，最终，我将需要更新表A，并使其如下所示

So eventually, I will need to update table A, and make it look like below

A
ID      NAME
1        John;Mark
2        null
3        Luke;Matthew
4        null
5        null

答

不同于 MySQL ， SQL Server 没有内置函数可以执行此操作。但是您仍然可以使用 CROSS APPLY 和 FOR XML PATH（''）

Unlike MySQL, SQL Server doesn't have a built-in function to do that. But you can still simulate it by using CROSS APPLY and FOR XML PATH('')

SELECT  a.ID, 
        SUBSTRING(d.NameList,1, LEN(d.NameList) - 1) Names
FROM    a
        CROSS APPLY
        (
            SELECT DISTINCT [NAME] + '; ' 
            FROM  B 
            WHERE A.ID = B.DupID 
            FOR XML PATH('')
        ) D (NameList)

相关推荐