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

db042190 1,416 Reputation points
2020-08-28T15:55:53.033+00:00

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--
                      t3,...
                      t4,...
                      t5,...
                      t6
{count} votes

Accepted answer
  1. Viorel 82,476 Reputation points
    2020-08-28T17:50:22.387+00:00

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

    select 
     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,416 Reputation points
    2020-08-28T19:10:15.513+00:00

    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.

    No comments

  2. Yitzhak Khabinsky 19,856 Reputation points
    2020-08-28T20:43:07.507+00:00

    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);
    
    No comments

  3. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2020-08-28T21:50:26.11+00:00

    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
    
    No comments

  4. Viorel 82,476 Reputation points
    2020-08-29T10:09:57.043+00:00

    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?

    No comments