elegant way of mapping the next non null from a set of address fields

db042190 1,436 Reputation points

hi, we run 2016 enterprise. i have six query target columns t1 thru t6 all varchar. i have 6 source columns s1 thru s6.

is there an elegant way of popping the next non null from s1 thru s6 and mapping it to the next target column?

the way i started writing the query should give you an idea of what i'm talking about...

select coalesce(s1,s2,s3,s4,s5,s6,'') t1,
              case when s1 is not null then coalesce(s2,s3,s4,s5,s6,'')
                      when s2 is not null then coalesce(s3,s4,s5,s6,'')
                      when s3 is not null then coalesce(s4,s5,s6,'')
                      when s4 is not null then coalesce(s5,s6,'')
                      else coalesce (s6,'') t2,
              case --not even sure what i would do here--
{count} votes

Accepted answer
  1. Viorel 88,576 Reputation points

    Check an example that is based on JSON, or give missing information:

     JSON_VALUE(j, '$[0].s') as t1,
     JSON_VALUE(j, '$[1].s') as t2,
     JSON_VALUE(j, '$[2].s') as t3,
     JSON_VALUE(j, '$[3].s') as t4,
     JSON_VALUE(j, '$[4].s') as t5,
     JSON_VALUE(j, '$[5].s') as t6
    from @mytable
    cross apply (
     select s 
     from (values (s1, 1), (s2, 2), (s3, 3), (s4, 4), (s5, 5), (s6, 6)) t(s, n)
     where s is not null
     order by n
     for json auto
    ) t(j)

    (See next comment with sample data).

5 additional answers

Sort by: Most helpful
  1. db042190 1,436 Reputation points

    thx all,

    from viorel-qna's data, my t1 would be address line 1, my t2 would be address line 2, my t3 would be address line 3, i dont care if my t4-t6 are nulls or blanks.

    i'll try viorel-qna's approach and post back here. i'm not sure how i would explain that solution.

  2. Yitzhak Khabinsky 20,646 Reputation points

    Thanks goes to Viorel for providing DDL and sample data population.

    Here is another solution based on XML.
    This method automatically removes NULL values from the XML.
    Exactly what you need.
    No need to do any joins, CROSS APPLY (VALUES) table value constructors, MIN() function calls, CASE statements, PIVOT, or any other sophisticated data manipulation gymnastics.

    -- DDL and sample data population, start
    DECLARE @mytable TABLE
         s1 VARCHAR(30),
         s2 VARCHAR(30),
         s3 VARCHAR(30),
         s4 VARCHAR(30),
         s5 VARCHAR(30),
         s6 VARCHAR(30)
    INSERT INTO @mytable VALUES 
    ( NULL, 'Address Line 1', NULL, NULL, 'Address Line 2', 'Address Line 3'),
    ( 'Address Line 1', NULL, 'Address Line 2', NULL, NULL, NULL);
    -- DDL and sample data population, end
    ;WITH rs (xmldata) AS
    SELECT * 
    FROM @mytable
    FOR XML PATH('r'), TYPE, ROOT('root') 
    SELECT c.value('(*[1]/text())[1]','VARCHAR(30)') AS t1
    , c.value('(*[2]/text())[1]','VARCHAR(30)') AS t2
    , c.value('(*[3]/text())[1]','VARCHAR(30)') AS t3
    , c.value('(*[4]/text())[1]','VARCHAR(30)') AS t4
    , c.value('(*[5]/text())[1]','VARCHAR(30)') AS t5
    , c.value('(*[6]/text())[1]','VARCHAR(30)') AS t6
    FROM rs CROSS APPLY xmldata.nodes('/root/r') AS t(c);
  3. Erland Sommarskog 72,416 Reputation points MVP

    Here is a decently elegant solution which does not use JSON or XML:

         declare @mytable table 
         (   id int,
             s1 varchar(30),
             s2 varchar(30),
             s3 varchar(30),
             s4 varchar(30),
             s5 varchar(30),
             s6 varchar(30)
         insert into @mytable values 
          ( 1, NULL, 'Address Line 1', NULL, NULL, 'Address Line 2', 'Address Line 3'),
          ( 2, 'Address Line 1', NULL, 'Address Line 2', NULL, NULL, NULL),
          ( 3, NULL, 'Address Line 1', NULL, NULL, 'Address Line 2', 'Address Line 3'),
          ( 4, 'Address Line 1', NULL, 'Address Line 2', NULL, NULL, NULL)
         select * from @mytable
    ; WITH CTE AS (
        SELECT m.id, ca.s, row_number() OVER (PARTITION BY m.id ORDER BY ca.n) AS rowno
        FROM   @mytable m
        CROSS  APPLY (VALUES(1, m.s1), (2, m.s2), (3, m.s3), 
                            (4, m.s4), (5, m.s5), (6, m.s6)) AS ca(n, s)
        WHERE  ca.s IS NOT NULL
    SELECT id,
           MIN(CASE WHEN rowno = 1 THEN s END) AS s1,
           MIN(CASE WHEN rowno = 2 THEN s END) AS s2,
           MIN(CASE WHEN rowno = 3 THEN s END) AS s3,
           MIN(CASE WHEN rowno = 4 THEN s END) AS s4,
           MIN(CASE WHEN rowno = 5 THEN s END) AS s5,
           MIN(CASE WHEN rowno = 6 THEN s END) AS s6
    FROM   CTE
    GROUP  BY id
  4. Viorel 88,576 Reputation points

    If the table has the id column, then also check a variation, which uses PIVOT:

    select id, [1] as t1, [2] as t2, [3] as t3, [4] as t4, [5] as t5, [6] as t6   
    from (  
    	select id, s, r  
    	from @mytable  
    	cross apply (  
    		select s, count(s) over (order by n) as r  
    		from (values (s1, 1), (s2, 2), (s3, 3), (s4, 4), (s5, 5), (s6, 6)) t(s, n)  
    	) t   
    ) t  
    pivot ( max(s) for r in ([1], [2], [3], [4], [5], [6]) ) as p  

    Do you have rows where all of s1–s6 are NULL?