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