declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @userId int = ; with groups as ( select distinct ownerId, ownerType, userId, '0:' + coalesce( nullif( userReportGroup, '' ), '$ungrouped' ) as savedReportKey, nullif( userReportGroup, '' ) as userReportGroup, case when nullif( userReportGroup, '' ) is null then 1 else 0 end groupRank from tbl_SYS_savedReports sr with (nolock) where sr.ownerId = @ownerId and sr.ownerType = @ownerType and sr.userId = @userId and sr.temporary = 'N' ), savedReports as ( select sr.groupRank, sr.savedReportKey, sr.userId, sr.userReportGroup, ( select srr.savedReportId, srr.userReportName, nullif( srr.userReportGroup, '' ) as userReportGroup, cast( srr.savedReportId as varchar ) + ':' + coalesce( nullif( srr.userReportGroup, '' ), '$ungrouped' ) as savedReportKey from tbl_SYS_SavedReports srr with (nolock) where srr.userId = sr.userId and coalesce( nullif( srr.userReportGroup, '' ), '$ungrouped' ) = coalesce( nullif( sr.userReportGroup, '' ), '$ungrouped' ) and srr.ownerId = sr.ownerId and srr.ownerType = sr.ownerType and srr.temporary = 'N' order by srr.userReportName for json auto, include_null_values ) savedReportsJSON from groups sr with (nolock) ), data as ( select groupRank, userId, savedReportKey, userReportGroup, json_query( savedReportsJSON ) as savedReports from savedReports ) #forJSON( 'data', attributes.datastore, 'groupRank,userReportGroup' )#