When last we checked in on The Technique That Lance Found, Adam had noted that the method entitizes XML special characters, a state of affairs which limits its utility somewhat.  I tried to leverage Tony Rogerson's technique, which Adam passed along in his comment, but it was late and I was tired, so that didn't go so well.  I provided a relatively lame proof-of-concept using nested REPLACEs and called it an evening.

Well, it's amazing what a couple of good nights' sleep will do (not to mention clearing several important deliverables from my plate).  I present herewith the Rogerson Permutation of The Technique That Lance Found:

-- PERFORM THE TRICK WITH THE ROGERSON MODIFICATION
-- PIVOT Parent VALUES INTO 1 COLUMN FOR 1 BASE ROW 

SELECT LEFT(Parent_CSV,LEN(Parent_CSV)-1) FROM (
    SELECT (SELECT mydata FROM (
            SELECT x AS [data()] FROM (
                SELECT ParentString + N', ' FROM (
                    SELECT  TOP 100 PERCENT Child.ParentId, Parent.ParentString
                    FROM    Child 
                    JOIN    Parent 
                    ON      Child.ParentId = Parent.ParentId  
                    WHERE   Child.ChildId = 2  -- MUST SPECIFY 1 BASE ROW.  COULD BE A CORRELATED SUBQUERY 
                    ORDER BY Child.ParentId
                ) a
            ) AS y (x) FOR XML PATH(''), TYPE
        ) AS d ( mydata ) FOR XML RAW, TYPE ).value( '/row[1]/mydata[1]', 'varchar(max)' )
      AS Parent_CSV
) a

The results here address Adam's concern:

I'm not wild about the TOP 100 PERCENT/ORDER BY combination, as that's something of a dirty trick.  When I have a little more time I'll look at using a CTE to get the ordered result.

Thanks to Adam for the link to Tony's work, and to Tony for having it there in the first place.

More to come..

-wp