I want to join the results of a distinct query so that I get 1 line per distinct entry. Let's say my table looks like this:
I want the result of my query to be:
Can this be done in entirely in SQL?
Yes, it can.
There's a handy trick with the XML handler that can be used to pull this stunt, and I've blogged about it in the past: http://thelonedba.wordpress.com/2011/02/22/string-concatenation-using-xml-path/
Damn. My blog post doesn't go far enough. Apologies... hang on...
OK, so the thing to do is to apply what I've done in the above post to each distinct ID. So something like this:
and that gives us: