Using XML Path in sql server 2008 for merge duplication rows in one row

Using XML Path with two different SQL function function REPLACE and STUFF
or

STUFF AND REPLACE FOR XML PATH 

String Concatenation

or 
Using XML PATH MERGE DUPLICATE IN ONE ROW

First i am creating a test table:

create table tblSubjects (id int identity(1,1), PersonID int, Unit varchar(10))

Inserting record in to the table:

insert into tblSubjects values (1,'Che')
insert into tblSubjects  values (1,'Mat')
insert into tblSubjects  values (1,'Phy')
insert into tblSubjects values (2,'Che2')
insert into tblSubjects  values (2,'Mat2')
insert into tblSubjects  values (2,'Phy2')
insert into tblSubjects  values (3,'Phy3')

Checking out record inserted:

select * from tblSubjects

Using XML PATH with REPLACE(path, 'oldstring', 'newstring') 

A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It can also be used in assignment statements.

We will discuss about these two sql function REPLACE and STUFF and more about XML PATH in my upcoming post .

SELECT t1.PersonID,
Units =REPLACE(
(SELECT Unit AS [data()] FROM tblSubjects t2
WHERE t2.PersonID = t1.PersonID ORDER BY Unit
FOR XML PATH(''))
, ' '
, ','
 )
FROM tblSubjects t1
GROUP BY PersonID ;

Using XML PATH with STUFF ( character_expression , start , length , replaceWith_expression )

SELECT DISTINCT PersonID,
STUFF(
(SELECT ',' + t.Unit FROM tblSubjects AS t
WHERE t.PersonID = tblSubjects.PersonID FOR XML PATH(''))
, 1
, 1
, ''
) AS Unit
FROM tblSubjects
ORDER BY PersonID

RESULT: