Return a list of related records in SQL Server
I absolutely love SQL (especially SQL Server) and I love how I continually find new ways to solve problems with it. I wish I could remember where I first found this tip to give proper credit, but I really love it.
Have you ever wanted to return a comma-delimited list of records in SQL Server? It turns out, that it is actually pretty easy to do.
For example, if you have users that can work in multiple regions and you need a list of which regions they work in.
STUFF (
SELECT ',' + CONVERT(varchar,RegionID)
FROM userregions
WHERE userregions.UserID = users.UserID
FOR XML PATH('')
,1,1,'') AS Regions
FROM users
WHERE 1 = 1
You will get back recordset of all of your users and each row will have a comma-delimited list of regions for that user. It will work for any subquery that returns a single varchar field.
That's really all you need. If you are interested in how it works, read on!
FOR XML
The FOR XML functionality of SQL Server is pretty neat by itself.
FROM widgets
FOR XML PATH('widget')
This will return a query with a really weird column name with the following content:
<WidgetID>1</WidgetID>
<WidgetName>Apple</WidgetName>
</widget>
<widget>
<WidgetID>2</WidgetID>
<WidgetName>Banana</WidgetName>
</widget>
<widget>
<WidgetID>3</WidgetID>
<WidgetName>Cherry</WidgetName>
</widget>
<widget>
<WidgetID>4</WidgetID>
<WidgetName>Date</WidgetName>
</widget>
If you wanted to make the query useful, then you could just give the resulting column a proper name.
SELECT WidgetID,WidgetName
FROM widgets
FOR XML PATH('widget')
) AS widgets
If you ditched the string passed in to FOR XML PATH, then you would end up with the following XML:
<WidgetName>Apple</WidgetName>
<WidgetID>2</WidgetID>
<WidgetName>Banana</WidgetName>
<WidgetID>3</WidgetID>
<WidgetName>Cherry</WidgetName>
<WidgetID>4</WidgetID>
<WidgetName>Date</WidgetName>
As XML, this isn't worth much but it helps us understand how this works.
What does SQL Server do, however, if it doesn't have a column name? (note the lack of "AS" for each column)
SELECT WidgetID + 0,
WidgetName + ''
FROM widgets
FOR XML PATH('widget')
) AS widgets
Then it just returns a messy string.
That is useless, but doesn't have to be.
SELECT CONVERT(varchar,WidgetID) + ':',
WidgetName + ';'
FROM widgets
FOR XML PATH('')
) AS damages
This returns the following:
Which, honestly, isn't that bad by itself.
Let's try it with just one field and see what kind of list we get.
SELECT CONVERT(varchar,WidgetID) + ','
FROM widgets
FOR XML PATH('')
) AS damages
That returns the following list for our example.
If you don't mind the trailing comma, this could actually work.
STUFF
STUFF is a really handy function with the following method signature:
So, if you had numbers representing a time string and wanted to inject a colon in them to make it more readable, you could do the following:
(Example from SQLServerTutorial.net)
In this case, as the "length" argument is zero, STUFF didn't replace anything and instead just inserted a character.
Putting it Together
So, to put this all together, all you need to do is move the comma to the start of the string and then use STUFF to remove it.
SELECT CONVERT(varchar,WidgetID) + ','
FROM widgets
FOR XML PATH('')
,1,1,'') AS damages
In this case, you start with the string ",1,2,3,4" and then tell stuff to replace the first character with an empty string.
Enjoy and let me know if you have any questions!
There are no comments for this entry.
[Add Comment]