CODEWITHSUNDEEP
sqlsql-serverxmlselectgroup-by
Noth
Noth

Reputation: 115

Group by first column with results from second column as xml

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

Answers (1)

Dan Field
Dan Field

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

Related Questions