sql两个表组合到一起，字符串拼接后放在最后一列上

 

Create Table #B(PersonID Int, B Varchar(5000))
Insert Into #B(PersonID, B)
Values(2,'AAAAAAAAAAAAAAAAAAAAA')

Insert Into #B(PersonID, B)
Values(1,'AAAAAAAAAAAAAAAAAAAAA')

Create Table #A(PersonID Int, A Varchar(5000), ID int identity(1,1))
Insert Into #A(PersonID, A)
values('1', 'AAAABBBCCC|||')

Insert Into #A(PersonID, A)
values('1', 'DDDD|||')

Insert Into #A(PersonID, A)
values('1', 'EEEE|||')

Insert Into #A(PersonID, A)
values('2', 'AAAABBBCCC|||')

Insert Into #A(PersonID, A)
values('2', 'DDDD|||')

Insert Into #A(PersonID, A)
values('2', 'EEEE|||')

Select * from #A

/**
Declare @A varchar(5000)
Set @A = ''
Select @A = @A + A
from #A
Select @A**/
/**
Select * from #A
UPdate #B Set B = ''

**/
Declare @I Int, @Row Int
Set @I = 1
Select @Row = COUNT(*) from #A
While @I < = @Row
Begin
UPdate #B Set B = B + '' + A
from #A A Inner join #B B On A.PersonID = B.PersonID
where A.ID = @I
SEt @I = @i + 1
end

Select * from #B

drop table #A
drop table #B

SELECT a.PatientID, c.OperationName INTO #tmp
FROM GHMTDB..tOPS_ReqMain a
INNER JOIN GHMTDB..tOPS_ReqDetail b ON b.OPSSN = a.OPSSN
INNER JOIN GHBaseDB..tOperation c ON c.OperationID = b.OperationID
WHERE a.PatientID = 5

Select *from #tmp

SELECT a.PatientID, OperationName = (
stuff(
(select ',' + OperationName from #tmp where PatientID = A.PatientID for xml path('')),
1,
1,
''
)
)
FROM #tmp a GROUP BY a.PatientID

Stuff

Select OperationName = (
select ',' + OperationName from #tmp where PatientID = A.PatientID for xml path(''))
from #tmp a GROUP BY a.PatientID

Select select ',' + OperationName from #tmp where PatientID = A.PatientID for xml path('')

SElect * from #A

Select '***' + A from #A For Xml Path('')