Distinct concatenation please 1

[peac3]

Hey Guys,

I would like to concatenate the flag column as below group by ID and name dynamically.
As the flag can appear multiple times

ID          Name         Flag
1           ABC           M
1           ABC           N
2           DEF           P
2           DEF           O
2           DEF           P
2           DEF           O

and the output of the flag needs to be ordered alphabetically as per below.

ID          Name         Flag
1           ABC           MN
2           DEF           OP

We don't know how many different flag it would be so we need dynamic concatenation.
Any help would be appreciated please.

Thank you,

 

[DaveInIowa]

DECLARE @Things TABLE (ID INT, Name CHAR(3), Flag CHAR(1))

INSERT INTO @Things VALUES (1,'ABC','M'), (1,'ABC','N'), (2, 'DEF', 'P'), (2, 'DEF', 'O'), (2, 'DEF', 'P'), (2, 'DEF', 'O')

;WITH DistinctThings AS (
SELECT DISTINCT * FROM @Things
)

SELECT ID, Name,
	   (SELECT '' + Flag FROM DistinctThings WHERE ID = t.ID AND Name = t.Name ORDER BY Flag ASC FOR XML PATH('')) AS Flags
  FROM DistinctThings t
 GROUP BY ID, Name