562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to use the FOR XML keywords in SQL to concatenate ids
Part three 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.
I'm gradually sneaking up on the final answer to the challenge I set at the start of this blog.
As I mentioned at the start of this blog, if you want to learn more about SQL, why not treat yourself to a place on one of our introductory or advanced SQL online or classroom courses?
What we want to do is to replace the selected text in this query:
Instead of displaying Answer here, we want to show the list of course ids, comma-separated.
This will allow us to show the course ids in the second column of the query output:
Here's what we get for now.
One last bit of SQL we'll need is the STUFF function, which takes four arguments:
|Argument||What it contains||Example|
|1||String to search||'Wise Owl Training'|
|2||Where to start||6|
|3||How many characters to replace||3|
|4||Text to put instead||'Frog'|
The example above could be shown using this statement:
-- rename Wise Owl
SELECT STUFF('Wise Owl Training', 6, 3, 'Frog')
This would produce this when run:
A better name? Perhaps not.
For our example, we can use the STUFF function to get rid of the opening comma at the start of each list.
So here's the final query:
-- show course ids for each person
SELECT ',' + CAST(innerTable.CourseId AS varchar(10))
FROM tblPerson AS innerTable
WHERE innerTable.PersonName = p.PersonName
FOR XML PATH('')
),1,1,'') AS Ids
tblPerson AS p
This produces the following results:
The output from running the above query.
This uses a correlated subquery to list out two columns for each person:
Hopefully this now makes sense!
|Parts of this blog|
25 Aytoun Street