UPDATED 22 November 2008 with Code Gallery URL.
A ZIP file of the scripts from the SQL Tricks: Insights from Microsoft IT presentation I’m giving in Barcelona this week is available for download here. The tricks will be familiar to long-time denizens of this space, as they’ve all been discussed here before; I hope the provision of sample scripts demonstrating each technique will make them even more useful to you.
As always, please let me know if you have any questions or suggestions.
And XML string concatenation trick!
I'm not too expirienced with SqlServer, but everyone knows this:
declare @a varchar(max)
select @a=isnull(@a+', '+parentstring,parentstring)
parent p join child c on p.parentid=c.parentid where c.childid=2
which yeilds the same results.
You're correct; the syntax you nominate would give the same result as the XML concatenation trick. However, your syntax is not ANSI standard, and is therefore not something that I'd encourage.
The XML concatenation trick is fully ANSI compliant, which is why I prefer it.
Thanks for your note!