How to Transform rows into columns in sql server

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

SQL query to find employees hired in last n months

Create table Employees

(

     ID int primary key identity,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int,

     HireDate DateTime

)

GO

Insert into Employees values('Mark','Hastings','Male',60000,'5/10/2014')

Insert into Employees values('Steve','Pound','Male',45000,'4/20/2014')

Insert into Employees values('Ben','Hoskins','Male',70000,'4/5/2014')

Insert into Employees values('Philip','Hastings','Male',45000,'3/11/2014')

Insert into Employees values('Mary','Lambeth','Female',30000,'3/10/2014')

Insert into Employees values('Valarie','Vikings','Female',35000,'2/9/2014')

Insert into Employees values('John','Stanmore','Male',80000,'2/22/2014')

Insert into Employees values('Able','Edward','Male',5000,'1/22/2014')

Insert into Employees values('Emma','Nan','Female',5000,'1/14/2014')

Insert into Employees values('Jd','Nosin','Male',6000,'1/10/2013')

Insert into Employees values('Todd','Heir','Male',7000,'2/14/2013')

Insert into Employees values('San','Hughes','Male',7000,'3/15/2013')

Insert into Employees values('Nico','Night','Male',6500,'4/19/2013')

Insert into Employees values('Martin','Jany','Male',5500,'5/23/2013')

Insert into Employees values('Mathew','Mann','Male',4500,'6/23/2013')

Insert into Employees values('Baker','Barn','Male',3500,'7/23/2013')

Insert into Employees values('Mosin','Barn','Male',8500,'8/21/2013')

Insert into Employees values('Rachel','Aril','Female',6500,'9/14/2013')

Insert into Employees values('Pameela','Son','Female',4500,'10/14/2013')

Insert into Employees values('Thomas','Cook','Male',3500,'11/14/2013')

Insert into Employees values('Malik','Md','Male',6500,'12/14/2013')

Insert into Employees values('Josh','Anderson','Male',4900,'5/1/2014')

Insert into Employees values('Geek','Ging','Male',2600,'4/1/2014')

Insert into Employees values('Sony','Sony','Male',2900,'4/30/2014')

Insert into Employees values('Aziz','Sk','Male',3800,'3/1/2014')

Insert into Employees values('Amit','Naru','Male',3100,'3/31/2014')

Here is the SQL Query that does the job

-- Replace N with number of months

Select *

FROM Employees

Where DATEDIFF(MONTH, HireDate, GETDATE()) Between 1 and N

How To Delete duplicate rows in sql

Delete duplicate rows in sql

Create table Employees

(

     ID int,

     Name nvarchar(50),

     Gender nvarchar(50),

     Salary int

)

GO

Insert into Employees values (1,'Ram', 'Male', 70000)

Insert into Employees values (1,'Ram', 'Male', 70000)

Insert into Employees values (1,'Ram', 'Male', 70000)

Insert into Employees values (2,'Ram', 'Male', 70000)

Insert into Employees values (2,'Ram', 'Male', 70000)

Insert into Employees values (2,'Ram', 'Male', 70000)

Insert into Employees values (3,'Priya', 'Female', 35000)

Insert into Employees values (3,'Priya', 'Female', 35000)

Query

WITH EmployeesCTE AS

(

   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) ASRowNumber

   FROM Employees

)

DELETE FROM EmployeesCTE WHERE RowNumber > 1

How to find nth highest salary in SQL Server using a Sub-Query

Create table Employees

(

     ID int primary key identity,

     Name nvarchar(50),

     Gender nvarchar(50),

     Salary int

)

Insert into Employees values ('Ram', 'Male', 70000)

Insert into Employees values ('Vijay', 'Male', 60000)

Insert into Employees values ('Siva', 'Male', 45000)

Insert into Employees values ('Ram', 'Male', 70000)

Insert into Employees values ('Jeeva', 'Male', 45000)

Insert into Employees values ('Saranya', 'Female', 30000)

Insert into Employees values ('Priya', 'Female', 35000)

Insert into Employees values ('Abdul', 'Male', 80000)

To find the highest salary it is straight forward. We can simply use the Max() function as shown below.

Select Max(Salary) from Employees

To get the second highest salary use a sub query along with Max() function as shown below.

Select Max(Salary) from Employees where Salary < (Select Max(Salary) from Employees)

To find nth highest salary using Sub-Query

SELECT TOP 1 SALARY

FROM (

      SELECT DISTINCT TOP N SALARY

      FROM EMPLOYEES

      ORDER BY SALARY DESC

      ) RESULT

ORDER BY SALARY

To find nth highest salary using Rank

WITH RESULT AS

(

    SELECT SALARY,

           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK

    FROM EMPLOYEES

)

SELECT TOP 1 SALARY

FROM RESULT

I have a Columnname as ID and its Row Values are 1,1,2,2 How to Replace the value of 1 as 2 and 2 as 1 in the table.

create procedure replace1122
as
begin
select * from relaceid
update relaceid set id =3 where id=1
update relaceid set id =1 where id=2
update relaceid set id =2 where id=3
select * from relaceid

end

                                           Original Table   and  table  After  Executing SP

To get manager name based on managerid and emp id IN SQL

SELECT * from empdetails 

SELECT  e.empid,e.empname,m.empname as managername

fROM  empdetails e left join empdetails m

on e.managerid = m.empid

TableName - empdetails 

How To return full table when no input parameter is supplied to the Stored Procedure in SQL

create PROCEDURE spg 
@emp_id bigint = NULL
AS
SELECT *
FROM employees
WHERE emp_id = ISNULL(@emp_id,emp_id)
GO

--ISNULL(expression,replacementvalue)
--SELECT * FROM employees WHERE emp_id = emp_id