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/mydata', '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..