Reputation: 115
So suppose I have a table defined as this:
CREATE TABLE #TmpExampleTable
(
id int identity(0,1),
method_id int,
entity_id int
)
and with sample data added to the table a select * returns this:
id method_id entity_id
0 105 39
1 105 43
2 9 92
3 9 572
4 9 1014
5 9 1427
6 9 1959
7 9 2266
I would like to transform the results returned from the following select statement:
select method_id,entity_id from #TmpExampleTable
method_id entity_id
105 39
105 43
9 92
9 572
9 1014
9 1427
9 1959
9 2266
into something like this:
method_id entities
105 <Entities><Entity id=39 /><Entity id=43 /></Entities>
9 <Entities><Entity id=572 /><Entity id=1014 /><Entity id=1427 /><Entity id=1959 /><Entity id=2266 /></Entities>
Is this even possible? I've tried various combinations of CTEs and FOR XML statements but cannot get this to format in the way that I want.
Upvotes: 1
Views: 53
Reputation: 21661
How's this?
CREATE TABLE #TmpExampleTable
(
id int identity(0,1),
method_id int,
entity_id int
)
INSERT #TmpExampleTable (method_id, entity_id) VALUES
(105 , 39 ),
(105 , 43 ),
(9 , 92 ),
(9 , 572 ),
(9 , 1014),
(9 , 1427),
(9 , 1959),
(9 , 2266)
select method_id, (select entity_id as '@id' from #TmpExampleTable WHERE (method_id = results.method_id) for xml path('Entity'), root('Entities'),TYPE)
from #TmpExampleTable results
group by method_id
drop table #tmpexampletable
Upvotes: 1