This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to use the FOR XML keywords in SQL to concatenate ids Part one of a three-part series of blogs
If you want to group items in a SQL query, showing a comma-delimited list of ids for each, generating an XML file is not the obvious place to start - but it should be. Using FOR XML in SQL to group and concatenate ids (this blog) Generating XML from SQL using FOR XML PATH Combining grouping, STUFF and FOR XML in a single query

How to use the FOR XML keywords in SQL to concatenate ids

Part one of a three-part series of blogs

If you want to group items in a SQL query, showing a comma-delimited list of ids for each, generating an XML file is not the obvious place to start - but it should be.

Using FOR XML in SQL to group and concatenate ids (this blog)
Generating XML from SQL using FOR XML PATH
Combining grouping, STUFF and FOR XML in a single query

Posted by Andy Brown on 27 September 2016

Using FOR XML in SQL to group and concatenate ids

This week I wanted to take a table like this (I've simplified the problem, but the underlying principles are the same):

This table shows for each person which course numbers they've attended.

And group the courses for each person to show this:

Here the courses are listed for each person.

The answer I found involves using the keywords FOR XML and the STUFF function:

-- show course ids for each person

SELECT

p.PersonName,

STUFF((

SELECT ',' + CAST(innerTable.CourseId AS varchar(10))

FROM tblPerson AS innerTable

WHERE innerTable.PersonName = p.PersonName

FOR XML PATH('')

),1,1,'') AS Ids

FROM

tblPerson AS p

GROUP BY

p.PersonName

It took me ages to decode this, so I decided to see if I could throw light on how it all works (the point of the rest of this blog).

There are more options for controlling the XML output produced by the FOR XML keywords than are covered in this blog, as this Simple Talk article shows.

Creating the sample data

If you want to try this out yourself, run this SQL script to generate the tblPerson table shown above:

-- create table of people attending courses

CREATE TABLE tblPerson(

PersonId int PRIMARY KEY,

PersonName varchar(8000),

CourseId int

)

-- add 5 people

INSERT INTO tblPerson(