search for last non-null field in a row?

Transact-SQL

lijun


Here is my simplified table/field for illustration purpose.

Id, Date, C1, C2, ..., C50
Id and Date are primary key. C1 to C50 are nullable.

Give Id, Date and a number between 1 to 50 ,say 35, I want to my stored-proc to return the field name and value of the last non-null value before column C35 in the row that is identified by Id and Date. Suppose C34 has value 100, I'd like to return C34 and 100, if C34 is null and C33 is not null with value 95, then I'd like to return C33 and 95...etc.

Since Id and Date are primary key, it can identified a unique row. Now the question is how to easily get the last non-null value

It would be easier if change the table structure but unfortunately it is legacy system and we can't change it.

Thanks.

Top

Re: search for last non-null field in a row?

DaleJ


This work's in SS2005

Code Snippet

create table #old (id int, date datetime, c1 int, c2 int, c3 int, c4 int)

insert into #old values (1, getdate()-10, null, 10, 20, null)

insert into #old values (2, getdate()-9, 1, 2, 3, null)

declare @id int,

@dt datetime,

@maxcol varchar(3)

set @id = 1

set @dt = (select date from #old where id = @id)

set @maxcol = 'c4'

; with legacydata

as

(

select *

from

(

select *

from #old

) as pvt

UNPIVOT

(Data FOR Col IN

(c1, c2, c3, c4)

)AS unpvt

)

select top 1 *

from legacydata

where id = @id

and date = @dt

and col < @maxcol

order by col desc






Top

Re: search for last non-null field in a row?

lijun

Thanks, DaleJ!

Yes, this works perfect on 2005. Is there any similar easy way on sql server 2000




Top

Re: search for last non-null field in a row?

DaleJ

Not quite a elegant.

You have to do the "Unpivot" manually.

Code Snippet

create table #old (id int, date datetime, c1 int, c2 int, c3 int, c4 int)

insert into #old values (1, getdate()-10, null, 10, 20, null)

insert into #old values (2, getdate()-9, 1, 2, 3, null)

declare @id int,

@dt datetime,

@maxcol varchar(3)

set @id = 1

set @dt = (select date from #old where id = @id)

set @maxcol = 'c4'

create table #legacydata( id int, date datetime, Data int, col varchar(10) )

insert into #legacydata

select id, date, c1, 'c1'

from #old

union all

select id, date, c2, 'c2'

from #old

union all

select id, date, c3, 'c3'

from #old

union all

select id, date, c4, 'c4'

from #old

delete #legacydata

where data is null

select top 1 *

from #legacydata

where id = @id

and date = @dt

and col < @maxcol

order by col desc

depending on the size of the data you're dealing with you may need a primary key on the id/date fields in the #legacydata table.





Top

Re: search for last non-null field in a row?

Steve Kass

Here an idea:


Code Snippet
select
id,
date,
case when c4 is not null then 'c4'
when c3 is not null then 'c3'
when c2 is not null then 'c2'
when c1 is not null then 'c1'
else null end as last_nonnull_column,
coalesce(c4, c3, c2, c1) as column_value
from Legacy_Table

Steve Kass

Drew University

www.stevekass.com


Top

Re: search for last non-null field in a row?

lijun

Worked. You save me tons of time!
Thanks so much!



Top

Re: search for last non-null field in a row?

hunchback

Try:

Code Snippet

declare @i int

declare @Id int

declare @Date datetime

set @Id = 17

set @Date = '20070703'

set @i = 34

select top 1

a.[Id],

a.[Date],

b.number as column_suffix,

'C' + ltrim(b.number) as [column_name],

case b.number

when 1 then c1

...

when 50 then c50

end as [column_value]

from

(

select [Id], [Date], c1, ..., c50

from dbo.t1

where [Id] = @Id and [Date] = @Date

) as a

cross join

(

select number

from (select 1 as number union all ... select 50) as [numbers]

where number <= @i

) as b

where

case b.number

when 1 then c1

...

when 50 then c50

end is not null

order by

b.number DESC

go

You have to expand the ellipsis.

AMB




Top