SQL Server Stuff function examples

Stuff function Example

declare @List1  table
(
RateNoticeId INT,
InHouse VARCHAR(100),
StateAccountID VARCHAR(100)
)
Insert into  @List1 values (10,'W070','2323')
Insert into  @List1 values (20,'W070','6552323')
Insert into  @List1 values (90,'W070','342323')
Insert into  @List1 values (70,'S070','92323')
Insert into  @List1 values (90,'S070','6552323')
Insert into  @List1 values (30,'S070','342323')
select * from @List1


select DISTINCT stuff(
        (
            Select DISTINCT ','+ CAST(ST1.RateNoticeId as varchar(100))
            From @List1 ST1
            Where ST1.InHouse = ST2.InHouse --and ST1.email=ST2.email
            For XML PATH ('')
        ), 1,1,'') [RateNoticeId],stuff(
        (
            Select DISTINCT ','+ CAST(ST1.StateAccountID as varchar(100))
            From @List1 ST1
            Where ST1.InHouse = ST2.InHouse --and ST1.email=ST2.email
            For XML PATH ('')
        ), 1,1,'') [StateAccountID],ST2.InHouse

from @List1 ST2