Btukfyl

My Data is structured like this:

and I'm trying to use STUFF/FOR XML PATH to concatenate fields.

If I use the examples that I find online I can get the following result:

But I wondered if the following is possible:

I am currently achieving this by calling FOR XML PATH twice, first to concatenate Header3:

and then again to get the desired result.

Is there a way to do it without calling XML PATH twice?

Are you looking for something like

CREATE TABLE T(

  Header1 INT,

  Header2 VARCHAR(45),

  Header3 VARCHAR(45)

);



INSERT INTO T VALUES

(123, 'A', 'aaa'),

(123, 'B', 'bbb'),

(123, 'C', 'ccc'),

(123, 'C', 'ddd'),

(456, 'E', 'eee');



WITH H3 AS

(

  SELECT DISTINCT Header1, Header2,

         STUFF(

           (

             SELECT ',' + Header3

             FROM T

             WHERE Header2 = H2.Header2

             FOR XML PATH('')

           ), 1, 1, ''

         ) Res

FROM T H2

)



SELECT DISTINCT

       Header1,

       STUFF(

         (SELECT ' '+ Header2 + ':' + Res + '|'

          FROM H3

          WHERE Header1 = TT.Header1

          FOR XML PATH('')

         ), 1, 1, ''

       ) Desired

FROM H3 TT;

Returns:

+---------+--------------------------+

| Header1 |         Desired          |

+---------+--------------------------+

|     123 | A:aaa| B:bbb| C:ccc,ddd| |

|     456 | E:eee|                   |

+---------+--------------------------+

Demo