mercredi 28 janvier 2015

Group element by another element?

The following SQL



with t(a, b) as (select 1, 2 union all select 1, 3)
select t.a as 'X/A/@Id',
t.b as 'X/A/b'
from t
for xml path(''), root('ROOT')


returns



<ROOT>
<X>
<A Id="1">
<b>2</b>
</A>
</X>
<X>
<A Id="1">
<b>3</b>
</A>
</X>
</ROOT>


Is it a way to group the two <b> into <a> since the Id(a) are the same? (Is it a way to do it without using subquery if possible?) Expected,



<ROOT>
<X>
<A Id="1">
<b>2</b>
<b>3</b>
</A>
</X>
</ROOT>

Aucun commentaire:

Enregistrer un commentaire