selecting last populated in a column sequence in a single row 2

[TheWkndr]

Goal: the last (which is not necessarily the highest) value in a group of columns for a row as part of a stored proc.

Columns:
UniqueID, data01, data02, data03, data04, data05

The columns will be populated in order (there will never be a row without a value in 02 that has a value in 03).

If the data looks like:
ID-abc, 12, 23, 34, 43
ID-def, 11, 22, 33

I need to have returned to me the values 43 and 33 respectively.

Thanks.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TheWkndr
Braccae tuae aperiuntur...

 

[TheWkndr]

Still learning after 5 yrs - I guess I was hoping for something a little more elegant than just a series of IF stmts.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TheWkndr
Braccae tuae aperiuntur...

 

[gmmastros]

You have 5 data columns. When 1 of them is 'blank', what is it's value? NULL?

[COLOR=blue]Select[/color] UniqueId, [COLOR=#FF00FF]Coalesce[/color](Data05, Data04, Data03, Data02, Data01) [COLOR=blue]As[/color] Data
[COLOR=blue]From[/color]   TableName

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Take a look at this example.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](UniqueId [COLOR=blue]VarChar[/color](20), Data01 [COLOR=blue]int[/color], Data02 [COLOR=blue]Int[/color], Data03 [COLOR=blue]int[/color], Data04 [COLOR=blue]int[/color], Data05 [COLOR=blue]int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'ID-abc'[/color], 12, 23, 34, 43, NULL)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'ID-def'[/color], 11, 22, 33, NULL, NULL)

[COLOR=blue]Select[/color] UniqueId, [COLOR=#FF00FF]Coalesce[/color](Data05, Data04, Data03, Data02, Data01) [COLOR=blue]As[/color] Data
[COLOR=blue]From[/color]   @Temp

This will ONLY work if there are NULL values for the 'missing' data.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[piti]

or if there are empty strings instead of null you can use the CASE WHEN statement

CASE
WHEN data05 <> '' THEN data05
WHEN data04 <> '' THEN data04
...
ELSE data01
END