declare @ownerId int = ; declare @ownerType varchar(3) = ; with uniqueTastewiseAttrs as ( select distinct ownerId, ownerType, skuId, tastewiseAttr, tastewiseValue from tbl_CRM_TastewiseProductAttributes with (nolock) where ownerId = @ownerId and ownerType = @ownerType ) , tastewiseAttributes as ( select ownerId, ownerType, skuId, tastewiseAttr, cast( '[' + string_agg( '''' + tastewiseValue + '''', ',' ) + ']' as varchar(250)) tastewiseValue from uniqueTastewiseAttrs with (nolock) where tastewiseAttr = 'related_dishes' and ownerId = @ownerId and ownerType = @ownerType group by ownerId, ownerType, skuId, tastewiseAttr union all select ownerId, ownerType, skuId, tastewiseAttr, cast( '[' + string_agg( '''' + tastewiseValue + '''', ',' ) + ']' as varchar(250)) tastewiseValue from uniqueTastewiseAttrs with (nolock) where tastewiseAttr = 'ingredients' and ownerId = @ownerId and ownerType = @ownerType group by ownerId, ownerType, skuId, tastewiseAttr ) , tastewiseProducts as ( select pt.ownerId, pt.ownerType, pt.skuId, pt.ingredients as tastewiseIngredients, pt.related_dishes as tastewiseRelatedDishes from tastewiseAttributes as ta pivot ( min( tastewiseValue ) for tastewiseAttr in ( [ingredients], [related_dishes] ) ) as pt ) , data as ( select ph.skuId, ph.sku, ph.skuDesc, ph.productHierarchyPath, tp.tastewiseIngredients, tp.tastewiseRelatedDishes, ph.sku + ' - ' + ph.skuDesc as skuFull from tastewiseProducts tp with (nolock) inner join [tbl_DW_ProductHierarchy#_tableSuffix#] ph with (nolock) on ph.skuId = tp.skuId and ph.ownerId = tp.ownerId and ph.ownerType = tp.ownerType where ph.crmActive = 'Y' ) #forJSON( 'data', attributes.datastore, 'sku' )#