SQL Server show column as delimited against a repeatable key

Question

Please help me write a SQL query. I have a table Name with these columns and sample data:

ID      FIRSTNAME    SURNAME       FULLNAME     
---------------------------------------------------    
1       JOHN         PETER         JOHN PETER   
2       PETER        JACKSON       PETER JACKSON
3       JOHN         PAUL          JOHN PAUL
3       JOHN         SECONDNAME    JOHN SECONDNAME

Fullname is first name + space + last name and ID can repeat.

I want to add another column OTHERNAMES: it will CONCATENATE with ALL firstname and surnames of a person (ID) has with delimiter ;

Table should look like this in the end:

ID    FIRSTNAME    SURNAME       FULLNAME         OTHERNAMES 
------------------------------------------------------------------------    
1     JOHN         PETER         JOHN PETER       JOHN PETER 
2     PETER        JACKSON       PETER JACKSON    PETER JACKSON
3     JOHN         PAUL          JOHN PAUL        JOHN PAUL;JOHN SECONDNAME
3     JOHN         SECONDNAME    JOHN SECONDNAME  JOHN PAUL;JOHN SECONDNAME

yes their values of FIRSTNAME + ' ' + SURNAME for EACH ID if they have REPEATING ID etc — Aiden, May 15 '17 at 10:13
e.g. contactenate with ; WHERE ID = 3 etc so EACH repeating ID will show their concatenated OTHERNAMES and as you know 1 and 2 do not repeat, we do not have their othernames — Aiden, May 15 '17 at 10:14
Possible duplicate of [How Stuff and 'For Xml Path' work in Sql Server](http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) — neer, May 15 '17 at 10:20

Shnugo · Accepted Answer · 2017-05-15T10:27:11.010

This design is awfull! Don't do that!

Just for completeness:

CREATE  TABLE MockUp(ID INT
                    ,FIRSTNAME VARCHAR(100)
                    ,SURNAME VARCHAR(100)
                    ,FullName AS FIRSTNAME + ' ' + SURNAME);

INSERT INTO MockUp VALUES  
 (1,'JOHN','PETER')
,(2,'PETER','JACKSON')
,(3,'JOHN','PAUL')
,(3,'JOHN','SECONDNAME');

SELECT ID 
      ,FIRSTNAME
      ,SURNAME
      ,FullName
      ,STUFF((SELECT '; ' + x.FullName FROM MockUp AS x WHERE x.ID=m.ID FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,'') AS OtherNames
FROM MockUp AS m
GO

DROP TABLE MockUp;

You see, that I added the FullName as computed column. The OtherNames are calculated on-the-fly. Do not store computeable data if you do not have a very good reason!

You are a genius and reason we are having technology :) – Aiden May 15 '17 at 10:27 — Aiden, May 15 '17 at 10:27

SQL Server show column as delimited against a repeatable key

1 Answers1