richard.noordam (11/27/2012)
first of all i was NOT looking for a specific solution. So your comment, and snarkyness are not appreciated, nor warranted.
If I came across as snarky I apologize. I think perhaps you misunderstood my coughing around a cursor.
I was indeed looking for a pattern of behavior to apply, to a problem i have, which this problem is a very minor piece of the whole problem that i'm trying to solve.
I was already using a coalesce statement with a select to generate the column list, and the solution wasn't to put the entire set of code here, and ask that it be tore apart. (FYI: another part is a crosstab using said column list, but that is outside of what i'm asking, which works awesome btw). Mainly because the problem that I asked about here can be stated simply.
What is the best way to deal with a set of rows and determine/return the first occurance of a non-zero column value in each row, using a set of column fields that are determined at runtime?
There should be a 'best-practice' on this, i would think, maybe not.
in any case.... there should be a pattern of behavior in assessing and attempting to solve, that doesn't rely on specific data.
You are looking for best practices in dealing with data is denormalized. I get that you have done this via dynamic cross tab but there isn't really a normal way of handling this. The best chance is the coalesce. An even better place in the process is to get your data the way you want it when you crosstab it initially.
So datasets were 'extra' and unnecessary, even though I provided in the second post.
What you posted is not a dataset. Nobody can load that into a table without first writing all the insert statements and create table statements. Keep in mind that we are all volunteers around here. When you don't post stuff in a consumable format the best anybody can do is take a shot in the dark.
Your post that contained your solution sounded very much like you wanted help with a better approach. You included selecting from a view (that we don't have), then updating a table (that we don't have). It seems that you want a magical solution to a problem without providing the details needed to solve it.
_______________________________________________________________
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/