Сорри если неправильно понял проблему, но на счет запроса вроде бы все просто :
PHP код:
declare @HIERARCHYrecid bigint = 68719485163
select
t1.DIMENSIONATTRIBUTEVALUESET
,STUFF((SELECT '-' + t16.NAME
FROM DIMENSIONATTRIBUTE t16
JOIN DIMENSIONHIERARCHYLEVELVIEW as t17
on t17.DIMENSIONATTRIBUTE = t16.RECID
where
t17.HIERARCHYrecid = @HIERARCHYrecid
ORDER BY t16.RECID
FOR XML PATH('')), 1, 1, '') as ConcatDimNames
,STUFF((SELECT '-' + -- t6.NAME +
ISNULL(
STUFF((SELECT '-' + t3.DisplayValue
from DimensionAttributeValueSetItemView as t3
where
t1.DimensionAttributeValueSet = t3.DimensionAttributeValueSet
and t7.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE
), 1, 1, '')
, 'N/A')
FROM DIMENSIONATTRIBUTE t6
JOIN DIMENSIONHIERARCHYLEVELVIEW as t7
on t7.DIMENSIONATTRIBUTE = t6.RECID
where
t7.HIERARCHYrecid = @HIERARCHYrecid
ORDER BY t6.RECID
FOR XML PATH('')), 1, 1, '') as ConcatValues
from DimensionAttributeValueSetItemView t1
join DIMENSIONHIERARCHYLEVELVIEW t4
on t4.DIMENSIONATTRIBUTE = t1.DIMENSIONATTRIBUTE
where
t4.HIERARCHYrecid = @HIERARCHYrecid
group by t1.DIMENSIONATTRIBUTEVALUESET
к стати один из блоков ORDER BY .. FOR XML PATH - был лишний, т.к. в DimensionAttributeValueSetItemView для DimensionAttributeValueSet, DIMENSIONATTRIBUTE - не будет повторений.