Pivot with multiple dynamic rows How do i get this?



to look like this:



my create and insert code :

Create Table Countries
(
    Country nvarchar(50),
    Town nvarchar(50),
    City nvarchar(50)
)
GO

Insert into Countries values ('USA','New Jersy','New York')
Insert into Countries values ('USA','houst','Houston')
Insert into Countries values ('USA','Dally','Dallas')

Insert into Countries values ('India','hyder','Hyderabad')
Insert into Countries values ('India','Bang','Bangalore')
Insert into Countries values ('India','Del','New Delhi')
Insert into Countries values ('India','townin','India Town')

Insert into Countries values ('UK','chelsea','London')
Insert into Countries values ('UK','sunderland','Birmingham')
Insert into Countries values ('UK','United','Manchester')


Current pivot code:

Select Country, town1, City1, town2, City2, town3, City3, town4, City4, town5, City5
From
(
Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence,
Town, 'Town'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence2 
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3, City4, City5) 
) Piv
pivot
(
max(Town)
for ColumnSequence2 in (town1, town2, town3, town4, town5) 
) Piv
group by Country, town1, City1, town2, City2, town3, City3, town4, City4, 
town5, City5
Simply get MAX value of Towns and Cities grouped by Country.




SELECT Country, 
       MAX(town1) Town1, MAX(City1) City1, 
       MAX(town2) Town2, MAX(City2) City2, 
       MAX(town3) Town3, MAX(City3) City3, 
       MAX(town4) Town4, MAX(City4) City4, 
       MAX(town5) Town5, MAX(City5) City5
FROM (
      SELECT Country, 
             City, 
             'City' + CAST(ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country) AS varchar(10)) ColumnSequence,
             Town, 
             'Town'+ CAST(ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country) AS varchar(10)) ColumnSequence2 
      FROM Countries
     ) Temp
PIVOT (MAX(City) FOR ColumnSequence IN (City1, City2, City3, City4, City5)) Piv
PIVOT (MAX(Town) FOR ColumnSequence2 IN (town1, town2, town3, town4, town5)) Piv
GROUP BY Country
GO



Country | Town1     | City1     | Town2      | City2      | Town3  | City3      | Town4  | City4      | Town5 | City5
:------ | :-------- | :-------- | :--------- | :--------- | :----- | :--------- | :----- | :--------- | :---- | :----
India   | hyder     | Hyderabad | Bang       | Bangalore  | Del    | New Delhi  | townin | India Town | null  | null 
UK      | chelsea   | London    | sunderland | Birmingham | United | Manchester | null   | null       | null  | null 
USA     | New Jersy | New York  | houst      | Houston    | Dally  | Dallas     | null   | null       | null  | null 

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



dbfiddle here