SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table vwLocations (PersonID int,	Address1 varchar(100),	Address2 varchar(100),	City varchar(100), State varchar(100),	Zip int);
insert into vwLocations (PersonID ,	Address1,City,State,Zip) values(1,	'123 main st',		'Home',	'CA',	90210);
insert into vwLocations (PersonID ,	Address1,City,State,Zip) values(1,	'456 main st',		'Home',	'CA',	90210);
insert into vwLocations (PersonID ,	Address1,City,State,Zip) values(1,	'789 main st',		'Home',	'CA',	90210);
insert into vwLocations (PersonID ,	Address1,City,State,Zip) values(2,	'123 main st',		'Home',	'CA',	90210);
insert into vwLocations (PersonID ,	Address1,City,State,Zip) values(2,	'456 main st',		'Home',	'CA',	90210);
insert into vwLocations (PersonID ,	Address1,City,State,Zip) values(2,	'789 main st',		'Home',	'CA',	90210);
insert into vwLocations (PersonID ,	Address1,City,State,Zip) values(3,	'123 main st',		'Home',	'CA',	90210);
7 rows affected

DECLARE @colAddress1 AS NVARCHAR(MAX),
    @colAddress2  AS NVARCHAR(MAX),
    @colCity  AS NVARCHAR(MAX),
    @colState  AS NVARCHAR(MAX),
    @colZip  AS NVARCHAR(MAX),
    @coltoshowAddress1 AS NVARCHAR(MAX),
    @coltoshowAddress2  AS NVARCHAR(MAX),
    @coltoshowCity  AS NVARCHAR(MAX),
    @coltoshowState  AS NVARCHAR(MAX),
    @coltoshowZip  AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX)
    
    
with cte as 
(
select distinct concat('Addr',row_number()over(partition by PersonId order by Address1),'Address1')NewAddress1,
concat('Addr',row_number()over(partition by PersonId order by Address1),'Address2')NewAddress2,
concat('Addr',row_number()over(partition by PersonId order by Address1),'City')NewCity, 
concat('Addr',row_number()over(partition by PersonId order by Address1),'State')NewState,
concat('Addr',row_number()over(partition by PersonId order by Address1),'Zip')NewZip
from vwLocations
)
select  @colAddress1 =string_agg( newaddress1,','),
        @colAddress2 =string_agg( newaddress2,','),
        @colCity =string_agg( newCity,','),
        @colState =string_agg( newState,','),
        @colZip =string_agg( newZip,','),
        @coltoshowAddress1 =string_agg( Concat('Max(',newaddress1,')',newaddress1),','),
        @coltoshowAddress2 =string_agg( Concat('Max(',newaddress2,')',newaddress2),','),
        @coltoshowCity =string_agg( Concat('Max(',newCity,')',newCity),','),
        @coltoshowState =string_agg( Concat('Max(',newState,')',newState),','),
        @coltoshowZip =string_agg( Concat('Max(',newZip,')',newZip),',')
from cte


set @query = 'SELECT PersonId,' +concat(@coltoshowAddress1 ,',', @coltoshowAddress2 ,',', @coltoshowCity ,',', @coltoshowState ,',', @coltoshowZip) + '
             from 
             (
                select *,concat(''Addr'',row_number()over(partition by PersonId order by Address1),''Address1'')NewAddress1,
concat(''Addr'',row_number()over(partition by PersonId order by Address1),''Address2'')NewAddress2,
concat(''Addr'',row_number()over(partition by PersonId order by Address1),''City'')NewCity, 
concat(''Addr'',row_number()over(partition by PersonId order by Address1),''State'')NewState,
concat(''Addr'',row_number()over(partition by PersonId order by Address1),''Zip'')NewZip
from vwLocations
            ) x
           pivot
            (
              max(Address1) for NewAddress1 in ('+@colAddress1+')
            )pivAddress1
           pivot
            (
              max(Address2) for NewAddress2 in ('+@colAddress2+')
            )pivAddress2
           pivot
            (
              max(City) for newCity in ('+@colCity+')
            )pivCity
           pivot
            (
              max(State) for NewState in ('+@colState+')
            )pivState
           pivot
            (
              max(Zip) for NewZip in ('+@colZip+')
            )pivZip
Group by PersonID'

			
execute(@query)


PersonId
Addr1Address1
Addr2Address1
Addr3Address1
Addr1Address2
Addr2Address2
Addr3Address2
Addr1City
Addr2City
Addr3City
Addr1State
Addr2State
Addr3State
Addr1Zip
Addr2Zip
Addr3Zip


1
123 main st
456 main st
789 main st
null
null
null
Home
Home
Home
CA
CA
CA
90210
90210
90210

2
123 main st
456 main st
789 main st
null
null
null
Home
Home
Home
CA
CA
CA
90210
90210
90210

3
123 main st
null
null
null
null
null
Home
null
null
CA
null
null
90210
null
null


Warning: Null value is eliminated by an aggregate or other SET operation.

PersonId	Addr1Address1	Addr2Address1	Addr3Address1	Addr1Address2	Addr2Address2	Addr3Address2	Addr1City	Addr2City	Addr3City	Addr1State	Addr2State	Addr3State	Addr1Zip	Addr2Zip	Addr3Zip
1	123 main st	456 main st	789 main st	null	null	null	Home	Home	Home	CA	CA	CA	90210	90210	90210
2	123 main st	456 main st	789 main st	null	null	null	Home	Home	Home	CA	CA	CA	90210	90210	90210
3	123 main st	null	null	null	null	null	Home	null	null	CA	null	null	90210	null	null