I have a table1 with
I need the query result as
Can anyone help me out?
What you are wanting is a join followed by a concatenation. Jeff Moden provides several examples of how to do this along with their performance implications in: Performance Tuning: Concatenation Functions and Some Tuning Myths ( http://www.sqlservercentral.com/articles/Test+Data/61572/ ). The last option he presents is normally the best.
Edit: I decided to go ahead and write a sample, but I have not tested this:
Again, I have not tested that, but it should be close to what you are asking for.
Edit2: Corrected the sample to use the third table. I still have not tested it. Thanks WilliamD.
with out using CTE
answered Oct 14, 2010 at 12:19 AM