USP_DATALIST_ACCOUNTINGELEMENTRELATIONSHIP

CREATE procedure dbo.USP_DATALIST_ACCOUNTINGELEMENTRELATIONSHIP
as
    set nocount on;

select AER.ID as ID, 
         AER.RELATIONSHIPID,
         AER.DESCRIPTION as DESCRIPTION,
         ASR.DESCRIPTION as RELATIONSHIPBASIS,
         AER.RESTRICTION,
         SELECTEDELEMENTS = case when len(substring((SELECT  DISTINCT ( ', ' + DE.SHORTDESCRIPTION )
                           FROM ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD
                           inner join PDACCOUNTSEGMENTVALUE DE on DE.ID = AERD.BASEELEMENTID
                           WHERE AER.ID = AERD.ACCOUNTINGELEMENTRELATIONSHIPID
                           ORDER BY 
                              ', ' + DE.SHORTDESCRIPTION
                            FOR XML PATH( '' )),3,105)) > 100 then
              substring((SELECT  DISTINCT ( ', ' + DE.SHORTDESCRIPTION )
                           FROM ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD
                           inner join PDACCOUNTSEGMENTVALUE DE on DE.ID = AERD.BASEELEMENTID
                           WHERE AER.ID = AERD.ACCOUNTINGELEMENTRELATIONSHIPID
                           ORDER BY 
                              ', ' + DE.SHORTDESCRIPTION
                            FOR XML PATH( '' )),3,100) + '...' else
              substring((SELECT  DISTINCT ( ', ' + DE.SHORTDESCRIPTION )
                           FROM ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD
                           inner join PDACCOUNTSEGMENTVALUE DE on DE.ID = AERD.BASEELEMENTID
                           WHERE AER.ID = AERD.ACCOUNTINGELEMENTRELATIONSHIPID
                           ORDER BY 
                              ', ' + DE.SHORTDESCRIPTION
                            FOR XML PATH( '' )),3,100) end
from ACCOUNTINGELEMENTRELATIONSHIP AER 
left join ACCOUNTINGELEMENTRELATIONSHIPDETAIL on AER.ID = ACCOUNTINGELEMENTRELATIONSHIPDETAIL.ACCOUNTINGELEMENTRELATIONSHIPID
left join dbo.PDACCOUNTSTRUCTURE ASR on ASR.ID = AER.BASEELEMENTACCOUNTSTRUCTUREID
GROUP BY AER.ID, AER.RELATIONSHIPID,AER.DESCRIPTION,AER.RESTRICTION,ASR.DESCRIPTION
order by AER.RELATIONSHIPID