T-SQL combining XML columns with same ID

Question

Hey all I have a question about combining like IDs that also have a XML column.

My data I'm trying to combine:

_ID    _xml                                                                                              _indivisualCommaList    _eachIndividual
------ ------------------------------------------------------------------------------------------------- ----------------------- ---------------
46589  <Individual><TBS>768-hER-382</TBS><Categories /><TBS2>768-hER-382,908-YTY-354</TBS2></Individual> 768-hER-382,908-YTY-354 768-hER-382
46589  <Individual><TBS>768-hER-382</TBS><Categories /><TBS2>768-hER-382,908-YTY-354</TBS2></Individual> 768-hER-382,908-YTY-354 908-YTY-354

Where

_ID = INT

_xml = XML

_indivisualCommaList = VARCHAR(MAX)

_eachIndividual = VARCHAR(MAX)

Pretty (easier to read) XML from above:

<Individual>
  <TBS>768-hER-382</TBS>
  <Categories />
  <TBS2>768-hER-382,908-YTY-354</TBS2>
</Individual>

<Individual>
  <TBS>768-hER-382</TBS>
  <Categories />
  <TBS2>768-hER-382,908-YTY-354</TBS2>
</Individual>

The XML, ID and _indivisualCommaList will always be the same no matter how many rows return back. The only unique column would be the _eachIndividual.

So I try the following query to group like IDs together

SELECT 
  * 
FROM 
  @tblData 
WHERE 
  _ID = @AssetID 
GROUP BY 
  _ID

Naturally, because of my XML column, I get the error of:

Column '@tblData._xml' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So I'm really not sure what I can do in order to combine these rows?

The end result I am looking to have is:

_ID    _xml                                                                                              _indivisualCommaList    _eachIndividual
------ ------------------------------------------------------------------------------------------------- ----------------------- -----------------------
46589  <Individual><TBS>768-hER-382</TBS><Categories /><TBS2>768-hER-382,908-YTY-354</TBS2></Individual> 768-hER-382,908-YTY-354 768-hER-382,908-YTY-354

SO, is this possible to do?

Answer 1

A solution (with horrible performance) without string_agg should be:

SELECT 
    dataA._id, 
    dataA._xml, 
    dataA._individualCommaList,
    CONCAT(dataA._eachIndividual,',',dataB._eachIndividual) as _eachIndividual
FROM data dataA
JOIN data dataB ON dataA._id = dataB._id AND dataA._eachIndividual != dataB._eachIndividual
WHERE dataA._individualCommaList = CONCAT(dataA._eachIndividual,',',dataB._eachIndividual)

db<>fiddle

JOIN the table onto itself to get the necessary data into one row, but only join different indivduals.
The WHERE Clauses ensures that the record with the correct order is kept.
Alternativley you could use an LIKE to keep the row from the first(?) indivdual in the list.