Access Groups by Door Association
SELECT accesslevel.name as "Access Group", acr.name as "Door"
FROM AccessLevelAcrScheduleMapping
inner join AccessLevel on AccessLevel.accessLevelUID = AccessLevelAcrScheduleMapping.accessLevelUID
inner join Acr on ACR.acrUID = AccessLevelAcrScheduleMapping.acrUID
WHERE AccessLevelAcrScheduleMapping.scheduleUID != (select scheduleUID from Schedule where name = 'Never')
ORDER BY ACR.name
SELECT acr.name as "Door", STUFF((SELECT ', ' + COALESCE(AccessLevel.name, '')
FROM AccessLevelAcrScheduleMapping
inner join AccessLevel on AccessLevel.accessLevelUID = AccessLevelAcrScheduleMapping.accessLevelUID
inner join Acr on ACR.acrUID = AccessLevelAcrScheduleMapping.acrUID
WHERE AccessLevelAcrScheduleMapping.scheduleUID != (select scheduleUID from Schedule where name = 'Never')
GROUP BY accesslevel.name
FOR XML PATH('') ), 1, 1, '') as "Access Groups"
FROM ACR
