SQL Pivot problems

If you are using sql server:

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

Query:

 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)

Output:

db<>fiddle here