• scott mcnitt (1/27/2010)


    MAV query is a very useful techique. I am sure it is something I am doing wrong (or is there a database setting I am missing) but I only get one column in my output when I should get 117.

    That's another problem with this technique: it is possible for a plan to be produced where the desired 'concatenation' doesn't work, and just one of the candidate values ends up in the variable. The query you posted happened to work 'correctly' on the instance I tested it with - but it is not guaranteed.

    Also, note that the ORDER BY clause in your query only applies to the final output of rows - it does not enforce an order for the multiple assignment operation.