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

Question

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

Answer 1

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).

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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?