Split contents of a field into multiple rows with a position/order assigned

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 14, 2014 at 8:15 am

  #1705772

  Lynn Pettis (4/14/2014)

  ---

  Sean Lange (4/14/2014)

  ---

  Lynn Pettis (4/14/2014)

  ---

  Sean Lange (4/14/2014)

  ---

  Lynn Pettis (4/9/2014)

  ---

  Sean Lange (4/9/2014)

  ---

  Lynn Pettis (4/9/2014)

  ---

  Well, looks like Sean and I have similar but different solutions.

  Yes indeed. One suggestion I would offer is to include an order by for your subquery to ensure you always get the same top rows. 😉

  Are you sure? Couldn't we say the same about yours? 😉

  I was saying that your subquery selects top X from your cte but there is no order by so it is conceivably possible that you could get values from your tally greater than len(Audit).

  Neither of us ordered the actual results. 😛

  Technically you are correct, we are relying on the ORDER BY in the ROW_NUMBER() OVER function to always return the data in the proper order. Seems to me that many of us rely on that when using the dynamic eTally in our solutions and have probably been luck that it always seems to work.

  Most likely with only 1,000 rows it should work. I just am probably hyper-sensitive because I got burned a number of years ago by assuming the order would be the clustered index. Since then I have been a bit OCD about the order by when using top.

  Well, like I said, we both should probably have used an ORDER BY in our subqueries up above. You may not have had a TOP in yours but still no guarantee that your would return in the proper order any more than mine. Agreed?

  110%!!!!

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/