SQL Interview Questions

This is another common sql server interview question. We will be using the following Countries table in this example.
transpose rows to columns sql

SQL to create the table
Create Table Countries
(
Country nvarchar(50),
City nvarchar(50)
)
GO

Insert into Countries values (‘USA’,’New York’)
Insert into Countries values (‘USA’,’Houston’)
Insert into Countries values (‘USA’,’Dallas’)

Insert into Countries values (‘India’,’Hyderabad’)
Insert into Countries values (‘India’,’Bangalore’)
Insert into Countries values (‘India’,’New Delhi’)

Insert into Countries values (‘UK’,’London’)
Insert into Countries values (‘UK’,’Birmingham’)
Insert into Countries values (‘UK’,’Manchester’)

Here is the interview question:
Write a sql query to transpose rows to columns. The output should be as shown below.
transform rows to columns sql

Using PIVOT operator we can very easily transform rows to columns
Select Country, City1, City2, City3
From
(
Select Country, City,
‘City’+
cast(row_number() over(partition by Country order by Country)
as varchar(10)) ColumnSequence
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3)
) Piv