tajrin (10/25/2013)
tnx for the reply, some more questions please:1...Is it possible to get 3 as a final result....ie the total number of different combinations?
2...why did you comment out the pipe separator (|)?
3...Is it possible to achieve the same result without using FOR XML path, because this is kind of slow.
tnx
See here for 1 SIGNIFICANT improvement to the XML version, and lots of other helpful information too: http://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx
Note that the xml stuff blows up if certain characters are in your data - another reason to perhaps avoid it. But the 2-order-of-magnitude perf improvement found in the link is a big deal obviously. I dislike XML also for both the data issue and the memory grant required.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service