Need a faster method to concatenate rows into column(s)--currently using CTEs
Looks like the issue in your code is multiple access to the CTEs and all the complicated logic you have in there. You should try to do everyting in a single pass with UNPIVOT/PIVOT. I did something similar here:
http://spaghettidba.com/2011/10/13/concatenating-multiple-columns-across-rows/
In my example you will find the concatenation of sentences in different languages (not exactly what you're after), but it seems to me that you could use the same technique.