Part 6 Transform rows into columns in sql server

Link for all dot net and sql server video tutorial playlists
kudvenkat

Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspo...

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
@aarvikitchen5572

This is another common sql server interview question. We will be using Countries table in this example.

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.

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

• #education
• #how_to
• #transform
• #transpose
• #rows
• #columns
• #sql_server
• #into
• #convert