SELECT      DISTINCT
        CLID,
        CDID,
        STUFF(( SELECT  '|' + SubTableDemographics.TextValue
                FROM    ( SELECT    CLID,
                                    CDID,
                                    TextValue
                          FROM      ClCxDemographicNoneExclusiveValues
                                    INNER JOIN CxDemographicsDefinedTextValues ON ClCxDemographicNoneExclusiveValues.CDDTVID = CxDemographicsDefinedTextValues.CDDTVID
                          WHERE     CLID IN ( 4586, 4587, 4588, 4589, 4590,
                                              4591, 4592, 4593, 10241, 10277,
                                              10306, 10350, 10351, 10354,
                                              10355, 10376, 10377, 10591,
                                              10593 )
                        ) SubTableDemographics
                WHERE   SubTableDemographics.CLID = TableDemographics.CLID
                        AND SubTableDemographics.CDID = TableDemographics.CDID
              FOR
                XML PATH('')
              ), 1, 1, '') AS DemographicValue
FROM    ( SELECT    CLID,
                    CDID,
                    TextValue
          FROM      ClCxDemographicNoneExclusiveValues
                    INNER JOIN CxDemographicsDefinedTextValues ON ClCxDemographicNoneExclusiveValues.CDDTVID = CxDemographicsDefinedTextValues.CDDTVID
          WHERE     CLID IN ( 4586, 4587, 4588, 4589, 4590, 4591, 4592, 4593,
                              10241, 10277, 10306, 10350, 10351, 10354, 10355,
                              10376, 10377, 10591, 10593 )
        ) TableDemographics
ORDER BY CLID, CDID