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.

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

Posted by Andy Brown on 27 September 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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):

Base table

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

 

And group the courses for each person to show this:

Ids grouped by name

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(

PersonId, PersonName, CourseId

) VALUES (

1, 'Tulisa Bush', 14

)

INSERT INTO tblPerson(

PersonId, PersonName, CourseId

) VALUES (

2, 'George Minogue', 23

)

INSERT INTO tblPerson(

PersonId, PersonName, CourseId

) VALUES (

3, 'George Minogue', 15

)

INSERT INTO tblPerson(

PersonId, PersonName, CourseId

) VALUES (

4, 'Tulisa Bush', 19

)

INSERT INTO tblPerson(

PersonId, PersonName, CourseId

) VALUES (

5, 'George Minogue', 6

)

-- show results

SELECT * FROM tblPerson

Running this script should give you the table shown.  Rather than hurrying full tilt towards the final answer, let's break the journey en route to look at what the FOR XML keywords actually do.

  1. Using FOR XML in SQL to group and concatenate ids (this blog)
  2. Generating XML from SQL using FOR XML PATH
  3. Combining grouping, STUFF and FOR XML in a single query
This blog has 0 threads Add post