Dynamic comparison of all columns for multiple rows

Question

In SQL Server 2008R2: What I am trying to do is take multiple rows from the same table and compare them to come up with the most complete row possible. I need the column lookup to be dynamic as the columns can change and access to the script that I am writing will not be easily alterable when live. The table has 277 columns (and counting), but let's try to simplify that for now:

REC  FIRST  LAST   MIDDLE    CITY     STATE    CTRY
1    John   Doe              Phoenix  UNK         
2    John   Doe    Quincy             AZ
3    J      Doe              Phoenix  AZ       USA  

I would like to be able to select a "master" row, for this example let's assume REC 1, and where the value is NULL, select the value from the next row, and if that is null, the next row, etc. for all selected rows. BONUS if I can not only overwrite NULLS, but where value = 'UNK' that will be my next step. Ideally, a combined record for the above would appear as such:

REC  FIRST  LAST   MIDDLE    CITY     STATE    CTRY
1    John   Doe    Quincy    Phoenix  AZ       USA

I have toyed around with EXCEPT and various UNIONS, but haven't made it very far out of the gates as the dynamic aspect of this is stunting my progress.

If it is not possible to do this with multiple rows as requested above, I am happy with comparing 1 to 2, and then comparing 1 to 3.

EDIT What I am trying to do is dynamically (and by dynamically, I mean to say that the columns and number of records may vary from occurrence to occurrence) create this:

create table #Something
(
    REC int
    , FIRSTName varchar(10)
    , LASTName varchar(10)
    , MIDDLE varchar(10)
    , CITY varchar(10)
    , STATE varchar(10)
    , CTRY varchar(10)
)

insert #Something
select 1, 'John', 'Doe', NULL, 'Phoenix', 'UNK', null union all
select 2, 'John', 'Doe', 'Quincy', NULL, 'AZ', null union all
select 3, 'J', 'Doe', NULL, 'Phoenix', 'AZ', 'USA'


select
          a.REC
         ,case
            when nullif(a.FIRSTName, 'UNK') is not null then a.FIRSTName
            when nullif(b.FIRSTName, 'UNK') is not null then b.FIRSTName
            when nullif(c.FIRSTName, 'UNK') is not null then c.FIRSTName
            else                                             a.FIRSTName
          end                       FirstName
         ,case
            when nullif(a.LASTName, 'UNK') is not null then a.LASTName
            when nullif(b.LASTName, 'UNK') is not null then b.LASTName
            when nullif(c.LASTName, 'UNK') is not null then c.LASTName
            else                                            a.LASTName
          end                       LastName
         ,case
            when nullif(a.MIDDLE, 'UNK') is not null then a.MIDDLE
            when nullif(b.MIDDLE, 'UNK') is not null then b.MIDDLE
            when nullif(c.MIDDLE, 'UNK') is not null then c.MIDDLE
            else                                          a.MIDDLE
          end                       MIDDLE
         ,case
            when nullif(a.CITY, 'UNK') is not null then a.CITY
            when nullif(b.CITY, 'UNK') is not null then b.CITY
            when nullif(c.CITY, 'UNK') is not null then c.CITY
            else                                        a.CITY
          end                       CITY
         ,case
            when nullif(a.STATE, 'UNK') is not null then a.STATE
            when nullif(b.STATE, 'UNK') is not null then b.STATE
            when nullif(c.STATE, 'UNK') is not null then c.STATE
            else                                         a.STATE
          end                       STATE
         ,case
            when nullif(a.CTRY, 'UNK') is not null then a.CTRY
            when nullif(b.CTRY, 'UNK') is not null then b.CTRY
            when nullif(c.CTRY, 'UNK') is not null then c.CTRY
            else                                        a.CTRY
          end                       CTRY
     from #Something    a
         ,#Something    b
         ,#Something    c
    where a.REC = 1
      and b.REC = 2
      and c.REC = 3

Answer 1

something to get you started.

declare

@x varchar(2) = '1' ,@sql varchar(max)

while @x < (select max(REC) from #Something)

begin set @sql = isnull(@sql,'')+ ' left join #Something x' + @x + ' on a.REC = x' + @x+'.REC - ' + @x

set @x = @x +1 end

set @sql =' select * from #Something a '+ @sql + ' where a.REC = 1'

exec (@sql)