合併兩條記錄

我有 2 張桌子

人表

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

事故表

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

我想為一個事件添加半列名稱

IncidentID Name 
1            ab;cd
2            ef;gh
3            ij

試試這個

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

關於 stuff() 命令的文件

引用自：https://dba.stackexchange.com/questions/239603