Hi All,

i have query like this.

SELECT RSLT.Column1,
STUFF((SELECT ',' + CAST(RSLT.Column2 AS NVARCHAR)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
FROM
(
SELECT 1 Column1, 1 Column2
UNION ALL
SELECT 1 Column1, 2 Column2
UNION ALL
SELECT 1 Column1, 3 Column2
UNION ALL
SELECT 2 Column1, 1 Column2
UNION ALL
SELECT 2 Column1, 2 Column2
UNION ALL
SELECT 2 Column1, 3 Column2
) RSLT

I am expecting below output

Column1 Column2

1 1,2,3

2 1,2,3

Please help me achieving this.

Thanks in advance.

with cte as (
SELECT  1 Column1, 1 Column2
UNION ALL
SELECT  1 Column1, 2 Column2
UNION ALL
SELECT  1 Column1, 3 Column2
UNION ALL
SELECT  2 Column1, 1 Column2
UNION ALL
SELECT  2 Column1, 2 Column2
UNION ALL
SELECT  2 Column1, 3 Column2
)
, rows as (
select distinct Column1 from cte
)
SELECT r.Column1,
STUFF((SELECT ',' + CAST(c.Column2 AS NVARCHAR)
from cte c
where c.Column1 = r.Column1
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
 FROM
rows r

I like  CROSS APPLY operator

WITH cte
AS
(
SELECT RSLT.Column1,
STUFF((SELECT ',' + CAST(RSLT.Column2 AS NVARCHAR)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') col2
FROM
(
SELECT  1 Column1, 1 Column2
UNION ALL
SELECT  1 Column1, 2 Column2
UNION ALL
SELECT  1 Column1, 3 Column2
UNION ALL
SELECT  2 Column1, 1 Column2
UNION ALL
SELECT  2 Column1, 2 Column2
UNION ALL
SELECT  2 Column1, 3 Column2
) RSLT
) 
SELECT DISTINCT
        Column1,
        STUFF(group_list, 1, 1, '') AS groups
FROM cte AS A
CROSS APPLY (SELECT ',' + [col2]
              FROM cte AS B
              WHERE B.Column1 = A.Column1
              FOR XML PATH('')) AS T(group_list);