0

I have two tables
1)Incident 

 GID                        UID <br>
------------          ----------------------
 178175721842183084   INC 1121035756

2)Incident_crew 

INCIDENT_GID            NAME
------------------     -----------------------------
178175721842183084     KENNEDY,KELLY 
178175721842183084     GRESLY,ANTHONY


Incident has a primary column called GID and Incident_crew is refering to this GID with the name Incident_GID. Incident_crew has two or more rows which are refering to same Incident_GID thus returns more than two rows. In Incident_crew table I have a column named NAME, I want to combine NAME from multiple rows and return it as one row seperated by semicolonenter image description here. The result should look something like below:

INCIDENT       GID                NAME
-------------  ------------------ ------------------------------ 
INC 1121035756 178175721842183084 KENNEDY, KELLY;GRESLY, ANTHONY
Squirrel
  • 23,507
  • 4
  • 34
  • 32
Ajaya Rk
  • 5
  • 1
  • 7
  • You probably want to check the answers here: https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – cddt Sep 08 '17 at 23:59

1 Answers1

0
SELECT INCIDENT, GID, NAME = STUFF(NAME, 1, 1, '')
FROM   Incident i
       CROSS APPLY
       (
           SELECT ';' + ic.NAME
           FROM  Incident_crew ic
           WHERE ic.INCIDENT_GID = i.GID
           FOR XML PATH('')
       ) n (NAME)
Squirrel
  • 23,507
  • 4
  • 34
  • 32