SQL Server : join and append columns

问题:

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

I have two tables: A and B.

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

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

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

回答1:

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) 

SQLFiddle Demo