February 22, 2013 at 1:55 pm
Hello
I have a table with 7 columns like Cumulative_cnt, AX1, AX2, AX3, AX4, AX5, POS
i want to find the first AX column with a 'null' and assign that column number to POS as a value
for e.g. if AX3 has the first null value then POS is assigned a value 3.
table is as follows
AX1-AX5 are varchar
all columns after the first column with a Null is Null
all columns preceding the first column with Null has a value
at least one column has a value and is not null which is usually AX1
February 22, 2013 at 2:02 pm
MadRazzi (2/22/2013)
HelloI have a table with 7 columns like Cumulative_cnt, AX1, AX2, AX3, AX4, AX5, POS
i want to find the first AX column with a 'null' and assign that column number to POS as a value
for e.g. if AX3 has the first null value then POS is assigned a value 3.
table is as follows
AX1-AX5 are varchar
all columns after the first column with a Null is Null
all columns preceding the first column with Null has a value
at least one column has a value and is not null which is usually AX1
This screams of poor design but the query is pretty simple.
Update YourTable
set POS =
case
when AX1 is not null then 1
when AX2 is not null then 2
when AX3 is not null then 3
when AX4 is not null then 4
when AX5 is not null then 5
end
_______________________________________________________________
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/
February 22, 2013 at 2:45 pm
what if ax2 through ax5 is null for a record
wouldn't the case statement assign value 5 to POS?
February 22, 2013 at 3:17 pm
MadRazzi (2/22/2013)
what if ax2 through ax5 is null for a recordwouldn't the case statement assign value 5 to POS?
no.
the case statement stops executing as soon as a match is found in the criteria is met.
other items are not evaluated. and they are evaluated in the order you put in the order that the case statement is created.
so it would return 2 in your example where 2-5 are null.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply