February 2009 Entries
Ever want to turn multiple rows into a comma seperated list when you are querying your database?
Here's a quick way to get it done using "FOR XML PATH('')":
DECLARE @comma_delimited_list VARCHAR(6000)
SELECT @comma_delimited_list =
(
SELECT productName + ','
FROM Products
WHERE productName LIKE 'a%'
FOR XML PATH('')
)
IF LEN(@comma_delimited_list) > 0
SELECT @comma_delimited_list = STUFF(@comma_delimited_list ,LEN(@comma_delimited_list), 1, '')
A couple things to remember: 1) you have set your path to '' and 2) you can't name your selection or it will put the name into your xml adding more information than you want.
The March 2009 issue of TechNet Magazine is out on the website. Check out my IT Toolbox column here:
http://technet.microsoft.com/en-us/magazine/dd440965.aspx
In this issue I covered:
TuneUp Utilities 2009: Tweak your system
Book: Configuration Manager 2007 Administrator’s Companion
HijackThis: Find problems and fix configuration errors
SysCompare Pro: Audit system configurations
Check it out and let me know what you think!