SQL Server : query output

Question

Kindly help me with below query output.

This is the desired output:

Please note that the xml node <Comp1_1> is combination of Comp+GroupID+SummaryID.

Code for sample table and data:

DROP TABLE IF EXISTS #Summary

CREATE TABLE #Summary
(
    Summaryid INT, 
    GroupID INT, 
    AnswerID INT, 
    Community VARCHAR(50), 
    ChildRecordID INT, 
    Features VARCHAR(100)
)

INSERT INTO #Summary(Summaryid, GroupID, AnswerID, Community, ChildRecordID, Features)
VALUES (1, 1, 15, 'Class A', 1, 'Good at baseball'),
       (2, 1, 16, 'Class C', 1, 'Passionate Gamer'),
       (2, 1, 16, 'Class C', 2, 'Nature Lover'),
       (3, 2, 17, 'Class D', 1, 'Loves doing Yoga')
       
SELECT * FROM #Summary     
   

Thanks in advance for looking up.

Cheers.

Answer 1

select 
SummaryId as 'SummaryID', GroupID as 'Group', AnswerID as 'AnswerID',
cast(concat('<Comp', GroupId, '_', SummaryId,'>', --concat for compxyz element name
(
--community per summary,group,answer
select y.Community,
(
    --childrows per community (per summary, group, answer)
    select z.ChildRecordID, z.Features
    from #Summary as z
    where z.Summaryid = x.Summaryid
    and z.GroupId = x.GroupID
    and z.AnswerID = x.AnswerID
    and z.Community = y.Community
    for xml path('ChildRecords'), type
)
from #Summary as y --this is not needed if there can be only one/single community per summary&group&answer..just add community in the outer groupby of table x and use columns of table x to get the childrows from table z
where y.Summaryid = x.Summaryid
and y.GroupID = x.GroupID
and y.AnswerID = x.AnswerID
group by y.Community
for xml path('') --if there can be many communities per summary&group&answer then this will result in <community a><childrows of a><community b><childrows of b>....
), 
'</Comp', GroupId, '_', SummaryId, '>') as xml)
from #Summary as x
group by Summaryid, GroupID, AnswerID
for xml path('SummaryDetails'), root('Summary');