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
ON Child.ParentId = Parent.ParentId
WHERE Child.ChildId = 2 — MUST SPECIFY 1 BASE ROW. COULD BE A CORRELATED SUBQUERY
ORDER BY Child.ParentId
) AS y (x) FOR XML PATH(”), TYPE
) AS d ( mydata ) FOR XML RAW, TYPE ).value( ‘/row/mydata’, ‘varchar(max)’ )
The results here address Adam’s concern:
|<Parent 1 String>,<Parent 2 String>,<Parent 3 String>|
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..