Using FOR XML to Concatenate multiple fields

Question

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?

Answer 1

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