Блог

У меня есть представление под названием vwLocations, которое содержит адреса для человека

столбцы: personid, адрес1, адрес2, город, штат, почтовый индекс

у меня может быть от 1 до многих адресов

Мне нужно сделать это в динамическом повороте, но я изо всех сил:

результаты должны быть

идентификатор лица, адрес1 aadr1, адрес2 aadr1, адрес2, адрес1, адрес2, адрес2, адрес2, адрес2, адрес2, адрес2, адрес2, адрес2, адрес…адрес1, адрес2, адрес2, адрес2, адрес1, адрес2, адрес2, адрес2

Редактировать:

учитывая этот ввод, мне понадобится следующий вывод

Если вы используете 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);
 

Запрос:

  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)
 

Выход:

бд<>скрипка <>здесь