Suggested Videos:
1 - How to find nth highest salary in sql
2 - SQL query to get organization hierarchy
3 - How does a recursive CTE work
4 - Delete duplicate rows in SQL
5 - SQL query to find employees hired in last n months
6 - Transform rows into columns in sql server
7 - SQL query to find rows that contain only numerical data
8 - SQL Query to find department with highest number of employees
9 - Difference between inner join and left join
This is another common sql server interview question. We will be using the following Countries table in this example.
SQL to create the table
Here is the interview question:
Write a sql query to transpose rows to columns. The output should be as shown below.
Using PIVOT operator we can very easily transform rows to columns
2 - SQL query to get organization hierarchy
3 - How does a recursive CTE work
4 - Delete duplicate rows in SQL
5 - SQL query to find employees hired in last n months
6 - Transform rows into columns in sql server
7 - SQL query to find rows that contain only numerical data
8 - SQL Query to find department with highest number of employees
9 - Difference between inner join and left join
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.
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
0 Comments