SQL Server : join and append columns

Question

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

Similar: stackoverflow.com/questions/10553198/…

SQLMason
– SQLMason

2012-09-27 15:11:41 +00:00
Commented Sep 27, 2012 at 15:11 — SQLMason
– SQLMason, Commented Sep 27, 2012 at 15:11

John Woo · Accepted Answer · 2012-09-27 15:10:36Z

4

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

edited Sep 27, 2012 at 15:10

answered Sep 27, 2012 at 15:04

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : join and append columns

1 Answer 1

SQLFiddle Demo

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related