Finding first search value across columns and retrieving column details it was found in

Question

Hi, I was wondering if it was possible to determine from which column a coalesce value is drawn from?

I have the following example data (actual years range from 1989 - 2010 in data, not shown for brevity)

ID    |   2000  | 2000 value | 2001  |2001 value |  2002  |2002 value | 2003  |2003 value |  2004  | 2004 value | 2005 | 2005 value
id001 |  single | 15.46      |regular|50         | NULL   | 0         |single | 152       | regular|15.20       |single| 15.99
id002 | regular | 20.46      |regular|17.99      |single  | 150.23    |both   |  256.3    | NULL   |  0         | NULL |  0

Where single / regular / both reflect how that ID paid for something in that year (and NULL represents no purchases).

What I would ideally like to have is a three columns per year for the years 2005-2010 tells you the most recent single payment type before that year (and in which year it falls), as well as a column for regular and both payment types

So for the example above the results would look like:

ID    |   2005 prior single year  |  2005 prior regular year  |  2005 prior both year
id001 |   2003                    |  2004                     | NULL
id002 |   2002                    |  2001                     | 2003

I would also like to be able to pull out the respective values as well (for all years 2005-2010).

Fundamentally it's just a case of looking across columns to find the first instance, but beyond some kind of coalesce I'm not sure how best to approach this!

Thanks! :)

Answer 1

First, I'd write a view to normalize the data:

select  2000 as year
,       [2000 value] as value
,       [2000 type] as type
from    YourTable
where   year = 2000
union all
select  2001
,       [2001 value]
,       [2001 type]
from    YourTable
where   year = 2001
....

Then you can look up the first year before 2005 like:

select  a.year [prior to 2005]
,       a.value
,       a.id
from    YourView a
where   year = 
        (
        select  max(year) 
        from    YourView b 
        where   a.id = b.id 
                and a.type = b.type 
                and b.year < 2005
        )

Once you have the normalized data, you can create many variations on this theme.

Answer 2

From the existing table, try:

select ID,
       case 'single'
           when [2004] then '2004'
           when [2003] then '2003'
           when [2002] then '2002'
           when [2001] then '2001'
           when [2000] then '2000'
           else NULL
       end [2005 prior single year],
       case 'regular'
           when [2004] then '2004'
           when [2003] then '2003'
           when [2002] then '2002'
           when [2001] then '2001'
           when [2000] then '2000'
           else NULL
       end [2005 prior regular year],
       case 'both'
           when [2004] then '2004'
           when [2003] then '2003'
           when [2002] then '2002'
           when [2001] then '2001'
           when [2000] then '2000'
           else NULL
       end [2005 prior both year]
from YourTable YT