Database Programming: The String Concatenation XML Trick
Courtesy of my good friend and once-and-always colleague, Lance Larsen, who writes:
I recently ran into this little trick. Joining two tables having a one-to-many relationship and stuffing a set of column values from the many side into a single column on the one side. Works only for one base row at a time – I found this as a correlated subquery in a much larger query. Might be able to do this as a function too but I thought it was interesting.
Here's a distillation of the code Lance ran into:
--LAY THE GROUNDWORK
--DROP TABLE Parent
create table Parent
(ParentID INT
,ParentString VARCHAR(100)
)
INSERT Parent VALUES (1, 'Parent 1 String')
INSERT Parent VALUES (2, 'Parent 2 String')
INSERT Parent VALUES (3, 'Parent 3 String')
-- DROP TABLE Child
create table Child
(ChildId INT
,ParentID INT
)
INSERT Child VALUES (1, 1)
INSERT Child VALUES (2, 1)
INSERT Child VALUES (2, 2)
INSERT Child VALUES (2, 3)
INSERT Child VALUES (3, 1)
INSERT Child VALUES (3, 3)
-- SHOW THE DATA
SELECT Child.ChildId, ISNULL(Parent.ParentString, '')
FROM Child
INNER JOIN Parent
ON Child.ParentID = Parent.ParentID
ORDER BY Child.ChildId, Child.ParentID
-- PERFORM THE TRICK
-- PIVOT Parent VALUES INTO 1 COLUMN FOR 1 BASE ROW
SELECT STUFF(( SELECT [text()]= ',' + ISNULL(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
FOR XML PATH('')), 1,1, '') AS Parent_CSV
This code produces two sets of output. The first shows the relationships between parents and children:
| ChildId |
(No column name) |
| 1 |
Parent 1 String |
| 2 |
Parent 1 String |
| 2 |
Parent 2 String |
| 2 |
Parent 3 String |
| 3 |
Parent 1 String |
| 3 |
Parent 3 String |
.. and the second shows the concatenated result for a single key value (2, in this case):
| Parent_CSV |
| Parent String 1, Parent String 2, Parent String 3 |
I find this to be an incredibly slick approach, both in its devious use of XML and its ingenious use of the STUFF function to remove the leading comma from the first concatenated value.
Thanks, Lance, for passing this along!
-wp
