BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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):
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(
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.
- 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