0

I have 2 tables

Person Table

PersonID   Firstname LastName
1              a         b
2              c         d
3              e         f
4              g         h
5              i         j

Incident Table

InicdentID PersonID 
1             1
1             2
2             3
2             4
3             5

I want to STUFF Name with semicolumn for one Incident

IncidentID Name 
1            ab;cd
2            ef;gh
3            ij
  • Do you want to just merge as part of a select statement, or do you want to place the merged rows into a (different) table? – Mark Stewart May 31 at 21:48
  • T-SQL can apply to two different platforms, SQL Server and Sybase/SAP ASE. What product, and which version thereof, are you using? – Andriy M Jun 8 at 11:05
3

Try this

create table #person (
PersonID int,
FirstName varchar(5),
LastName varchar(5)
)

create table #incident (  
IncidentID int,
PersonID int
)

insert into #person values (1,'a','b')
insert into #person values (2,'c','d')
insert into #person values (3,'e','f')
insert into #person values (4,'g','h')
insert into #person values (5,'i','j')

insert into #incident values (1,1)
insert into #incident values (1,2)
insert into #incident values (2,3)
insert into #incident values (2,4)
insert into #incident values (3,5)


 select t.IncidentID,
   stuff(
    (select ';' + p.firstname + p.lastname
      from #incident i
      join #person p
        on i.personid = p.personid
      where i.incidentid = t.incidentid
     for xml path('')),
     1,
     1,
     ''
   )
 from #incident t
group by t.IncidentID

Documentation on stuff() command

  • The actual goodnes in the solution above is the for xml path('') statement. The stuff() (while necessary to answer the question) just removes the redundant semi-colon – Trubs Jun 2 at 22:06
  • Thanks kevinnwhat I have developed the same but the difference is I was joining on PersonID everytime "where i.incidentid = t.incidentid". This saved me time. Thanks much – Hanu Jun 3 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.