使用多個動態行進行數據透視

我怎麼得到這個？

看起來像這樣：

我的創建和插入程式碼：

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

目前樞軸程式碼：

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

只需獲取按國家/地區分組的城鎮的 MAX 值。

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

國家 | 鎮1 | 城市1 | 鎮2 | 城市2 | 鎮3 | 城市3 | 鎮4 | 城市4 | 鎮5 | 城市5
:------ | :-------- | :-------- | :--------- | :--------- | :----- | :--------- | :----- | :--------- | :---- | :----
印度 | 海德 | 海得拉巴 | 砰 | 班加羅爾 | 德爾 | 新德里 | 鎮寧| 印度鎮 | *空* | *null* 
英國 | 切爾西 | 倫敦 | 桑德蘭| 伯明翰 | 聯合 | 曼徹斯特 | *空* | *空* | *空* | *null* 
美國 | 新澤西州 | 紐約 | 豪斯 | 休斯頓 | 達利 | 達拉斯 | *空* | *空* | *空* | *空值* 

警告：空值被聚合或其他 SET 操作消除。

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/172534