0

How do i get this?

PIV B4

to look like this:

PIV After

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.