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.

This'll require dynamic SQL, due to the dynamic nodes prefixed with Comp. — Thom A
– Thom A ♦, Commented Apr 14, 2021 at 13:46
Myzus, as @Lamu already pointed out, it is not a best practice to have XML element names to hold some running numbers. If you really need it, it is better to use XML attributes for that. Along the following: <Comp id="1"> — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Apr 14, 2021 at 14:10
Thanks for pointing out but we have this weird scenario which can be fulfilled only with above output. I got close but output is not correct. @All - Please share if anyone can come up with the query. Cheers. — Myzus
– Myzus, Commented Apr 14, 2021 at 16:21

lptr · Accepted Answer · 2021-04-14 17:54:47Z

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');

Collectives™ on Stack Overflow

SQL Server : query output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related