declare @ownerId int = ; declare @ownerType varchar(3) = ; with itemIds as ( select i.ownerId, i.fsl_tablecode, i.itemId, string_agg( '##'+cast( cl.mstr_attrId as varchar ) + ';', '' ) as categoryIds, string_agg( '##'+cast( sl.mstr_attrId as varchar ) + ';', '' ) as sectionIds from tbl_CMM_Items i with (nolock) left outer join tbl_ORG_AttributeLinks cl with (nolock) on cl.child_attrID = i.itemid and cl.child_attrTypeID = 24 and cl.mstr_attrTypeID = 26 left outer join tbl_ORG_AttributeLinks sl with (nolock) on sl.child_attrID = i.itemid and sl.child_attrTypeID = 24 and sl.mstr_attrTypeID = 25 where i.ownerId = @ownerId and i.fsl_tablecode = @ownerType group by i.ownerId, i.fsl_tablecode, i.itemId ), items as ( select cc.itemId, cc.title, nullif( cc.subTitle, '' ) subTitle, nullif( cc.text, '' ) as [text], nullif( cc.caption, '' ) caption, cc.publishDate, cc.unpublishDate, nullif( cc.teaserTxt, '' ) teaserTxt, cast( case when getDate() between cc.publishDate and cc.unpublishDate then 1 else 0 end as bit ) published, i.categoryIds, i.sectionIds from tbl_CMM_Items cc with (nolock) inner join itemIds i on i.ownerId = cc.ownerId and i.fsl_tablecode = cc.fsl_tablecode and i.itemId = cc.itemId where cc.ownerId = @ownerId and cc.fsl_tablecode = @ownerType ) #forJSON( 'items', attributes.datastore, 'publishDate,unpublishDate,itemId' )#