This is exactly what I was trying to do. Thanks very much. The grouping was done by a CRM app but it also nested the content and the formatting was...

Thanks for the feedback everyone, you were correct about the expected results, there should be a 1 for both swimming  and gymnastics for id 1. I'll explore these suggestions and...

I was able to adapt this to fit my needs. Thanks for your feedback as I had not tried an outer apply with a cte

Hi Chrissy,

You were right about the null value. I didn't think there could be null values returned in the query but there were. Thanks

The problem seems to have gone away on its own as of last week. No indication why as we didn't make any changes in SQL or Azure

l looked through the Portal and the link but it seemed more designed for Azure SQL whereas we have an Azure VM with SQL on it. The screenshots and menu...

I stopped the SQL Advanced threat protection traffic extended event session and killed the SQL ATP for Queries process in the Activity Monitor but it immediately started up and was...

Thanks for getting back to me Grant. We are not using Query Store but there are some extended events, including the built in Health and Telemetry Events.  Are there any...

Thanks for your reply Jeff. I put the Select statement in and I could see the @result value changing as it was following IF paths it shouldn't because the expected...

Figured out after lots of trial and error. Result posted in case anyone else trips over the syntax and this helps

select

m.c.value('@docid', 'int') as docid,

m.c.value('@docname', 'varchar(200)') as docname,

m.c.value('@transittype', 'varchar(200)') as transittype,

m.c.value('@doccontrol','varchar(20)')...

I'm sorry I wasn't clear on what I was trying to do. Essentially, I was having issues trying to Group the data in the three tables shown so I could...

That should work. Thanks

Thanks for the direction. I was able to adapt this to work for me.

That was it. Thanks for the pointer

That was it. Thanks Mark