If keeping those rows is important... This doesn't seem to beat it up too bad...

IF OBJECT_ID('tempdb..#address') IS NOT NULL

DROP TABLE #address;

CREATE TABLE #address (

address_id INT,

addr1 CHAR(30),

addr2 CHAR(30),

addr3 CHAR(30),

addr4 CHAR(30),

addr5 CHAR(30),

addr6 CHAR(30)

)

insert into #address values (1, '', '111 Main Street', '', 'Main City', 'CA', '92222')

insert into #address values (2, '', '', '', 'HB', 'CA', '92222')

insert into #address values (3, '', '', '', '', NULL, '')

insert into #address values (6, 'Blue', '', '', 'Costa Mesa', 'CA', '92222')

-- The actual solution --

SELECT

x.address_id,

MAX(CASE WHEN x.PartNum = 1 THEN NULLIF(x.PartName, '') END) AS Col1,

MAX(CASE WHEN x.PartNum = 2 THEN NULLIF(x.PartName, '') END) AS Col2,

MAX(CASE WHEN x.PartNum = 3 THEN NULLIF(x.PartName, '') END) AS Col3,

MAX(CASE WHEN x.PartNum = 4 THEN NULLIF(x.PartName, '') END) AS Col4,

MAX(CASE WHEN x.PartNum = 5 THEN NULLIF(x.PartName, '') END) AS Col5,

MAX(CASE WHEN x.PartNum = 6 THEN NULLIF(x.PartName, '') END) AS Col6

FROM (

SELECT

a.address_id,

ROW_NUMBER() OVER (PARTITION BY a.address_id ORDER BY p.PartNum) AS PartNum,

p.PartName

FROM

#address a

CROSS APPLY (

VALUES (1, a.addr1), (2, a.addr2), (3, a.addr3), (4, a.addr4), (5, a.addr5), (6, a.addr6)

) p (PartNum, PartName)

WHERE

p.PartName <> '' OR CONCAT(a.addr1, a.addr2, a.addr3, a.addr4, a.addr5, a.addr6) = ''

) x

GROUP BY

x.address_id

address_id Col1 Col2 Col3 Col4 Col5 Col6

----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

1 111 Main Street Main City CA 92222 NULL NULL

2 HB CA 92222 NULL NULL NULL

3 NULL NULL NULL NULL NULL NULL

6 Blue Costa Mesa CA 92222 NULL NULL