Converting rows into columns BY PIVOT

 --DAY 6
--#15DAYS CHALLENGE
--
Converting rows into columns BY PIVOT 
--Without using PIVOT

------------------------------SAMPLE TABLE CREATED---------------------------------------
SELECT REPLICATE('*',150) AS 'SAMPLE TABLE CREATED'
create table players_location
(
name varchar(20),
city varchar(20)
);
--delete from players_location;
insert into players_location
values ('Sachin','Mumbai'),('Virat','Delhi') , ('Rahul','Bangalore'),('Rohit','Mumbai'),('Mayank','Bangalore');
SELECT REPLICATE('*',150) AS 'INPUT_TABLE'
SELECT * FROM players_location
------------------------------------------------SOLUTION--------------------------------------------------------------
SELECT REPLICATE('*',150) AS 'SOLUTION START WITH PIVOT '

select * FROm 
(
SELECT name,city,ROW_NUMBER() OVER (PARTITION BY City ORDER BY Name) AS rn
FROM players_location
) A
PIVOT(
MAX(Name) FOR City IN ([Mumbai],[Bangalore],[Delhi]
)
)AS PivotTable

SELECT REPLICATE('*',150) AS 'SOLUTION START WITH OUT  PIVOT '

WITH tmp
     AS (SELECT *,
                Row_number()
                  OVER (
                    partition BY city
                    ORDER BY NAME ) rnk
         FROM   players_location)
SELECT rnk,
       Max(CASE
             WHEN city = 'Bangalore' THEN NAME
           END) Bangalore,
       Max(CASE
             WHEN city = 'Delhi' THEN NAME
           END) Delhi,
       Max(CASE
             WHEN city = 'Mumbai' THEN NAME
           END) Mumbai
FROM   tmp
GROUP  BY rnk 

SELECT REPLICATE('*',150) AS 'END OF SOLUTION'

------------------END OF THE SOLTION--------------------------