Transform multiple row values to 1 row values in sql server

Introduction:

Here i will explain  Transform multiple row values to 1 row values in sql server.

Description:

previous article i will explain Sql Function. Now today i will explain  the most of difficult query in sql server is Transform multiple row values to 1 row values in sql server.

Table:

CREATE TABLE Table1
 ([Id] int, [dwCode] varchar(5), [PRNo] varchar(5))
;
 
INSERT INTO Table1
 ([Id], [dwCode], [PRNo])
VALUES
 (1, 'DW001', 'PR001'),
 (2, 'DW001', 'PR002')
;

Query:

SELECT 
  t1.dwCode,
  STUFF((
    SELECT ', ' + t2.PRNo
    FROM Table1 t2
    WHERE t2.dwCode   = t1.dwCode
    FOR XML PATH (''))
  ,1,2,'') AS PRNo
FROM Table1 t1
GROUP BY t1.dwCode;