LeetCode Hard SQL problem | Students Reports By Geography | Pivot Ka Baap

[@debmalyapanday]

Hello Ankit, as usual, your explanation of the problem and its solution is excellent 🙂 Here's how I approached this in PostgreSQL: 1. At first, aggregated the rows into a single row using STRING_AGG (NULLs are eliminated) 2. Then, converted each column into array using STRING_ARRAY (So that we can use UNNEST function, as UNNEST takes array as input) 3. Then, used UNNEST to flatten each column into multiple rows. SELECT UNNEST(names_bangalore) AS Bangalore, UNNEST(names_mumbai) AS Mumbai, UNNEST(names_delhi) AS Delhi FROM ( SELECT STRING_TO_ARRAY(STRING_AGG(CASE WHEN city = 'Bangalore' THEN name END, ',' ORDER BY name), ',') AS names_bangalore, STRING_TO_ARRAY(STRING_AGG(CASE WHEN city = 'Mumbai' THEN name END, ',' ORDER BY name), ',') AS names_mumbai, STRING_TO_ARRAY(STRING_AGG(CASE WHEN city = 'Delhi' THEN name END, ',' ORDER BY name), ',') AS names_delhi FROM players_location ) AS subquery;

[@shubhamraj3520]

Hi Ankit, l was searching for pivoting explanation or another proper way to handle this type of question and the best part is my search came to an end. This is the best explanation I ever got for pivoting.❤❤❤

[@ankitbansal6]

😊

[@narenkrishh7412]

Initially I thought it can be easily done only with case statement. Later realised the problem. Good job brother!!

[@ankitbansal6]

Right

[@mantisbrains]

select player_groups, max(case when city = 'Bangalore' then name end) as Bangalore, max(case when city = 'Mumbai' then name end) as Mumbai , max(case when city = 'Delhi' then name end) as Delhi from (select *, row_number() over (partition by city order by name) as player_groups from players_location)a group by player_groups order by player_groups; Thanks Ankit !

[@2412_Sujoy_Das]

Sir, got so close to your solution exactly but couldn't figure out a way to aggregate them. Learned a new trick today!!!!!!

[@ankitbansal6]

Well done!

[@anirvansen2941]

MYSQL solution with base as ( select * , row_number() over(partition by city) as rnk from players_location ) select max(case when city ='Bangalore' then name else NUll end) as Banglalore, max(case when city ='Mumbai' then name else NUll end )as Mumbai, max(case when city ='Delhi' then name else null end ) as Mumbai from base group by rnk

[@ssaaurabh456]

first i was thinkin why you use row_number but after solving it by myself then i understand awesome content......

[@NinjaFox0257]

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

[@ankitbansal6]

Thanks for posting 👏

[@tejaskharde9122]

Great explanation Ankit , i am finding the advanced problem very difficult to understand but after your detail explanation it's more clear for me now ,thanks!

[@amrendrabaahubali6734]

what if there are 10-20 different cities like this, then I don't think so this method is feasible

[@rishav144]

true

[@gdebadutta4973]

Thank you . This cleared all my doubts regarding pivot in mysql.

[@ranjeetpawar2236]

Hi Ankit, Thanks for the video. I tried with PIVOT in Oracle. SELECT Bangalore,Mumbai,Delhi FROM ( SELECT name, city, row_number() over(partition by city order by name) rnum FROM players_location ) PIVOT ( min(name) FOR city IN('Mumbai' Mumbai,'Delhi' Delhi,'Bangalore' Bangalore) );

[@ankitbansal6]

Looks good. Thanks for posting

[@hamzakazmi5150]

My Solution: with yourtable as ( select *,row_number() over (partition by city order by city) as rn from players_location ), cte as ( select rn, Mumbai,Delhi,Bangalore from ( select city, rn, name from yourtable ) d pivot ( max(name) for city in (Mumbai,Delhi,Bangalore) ) piv ) select Bangalore,Mumbai,Delhi from cte

[@hemantsethiya1554]

with ct as ( Select *, row_number() over(partition by city order by name) as rn from players_location ) Select max(case when city = 'Bangalore' then name end) as Bangalore, max(case when city = 'Mumbai' then name end) as Mumbai, max(case when city = 'Delhi' then name end) as Delhi from ct group by rn order by rn

[@rajkumarrajan8059]

Can we create the city name dynamically? Because in this solution we are manually using the values as Mumbai, Bangalore, Delhi

[@dattatreyakulkarni541]

Yes, we we should add city as dynamically

[@ashwingupta4765]

with cte as ( Select * , ROW_NUMBER() over(partition by city order by name) as player_grp from players_location) Select max(case when city ='Bangalore' then name end) as bangalore, max(case when city ='Delhi' then name end) as Delhi, max(case when city ='Mumbai' then name end) as Mumbai from cte group by player_grp

[@shravyasuvarna1229]

with play as (select *,row_number() over (partition by city order by name asc) as rn from players_location order by rn) select min(case when city='Bangalore' then name end) as Bangalore, min(case when city='Mumbai' then name end) as Mumbai, min(case when city='Delhi' then name end) as Delhi from play group by rn ;

[@priyankasarkar6600]

Awesome explanation, Ankit sir, but yes, my question is also: if I have 20 cities, then how can we handle this situation? I have one dynamic solution, which I am posting here with the help of AI. For a better approach, please guide us. DECLARE @cols AS NVARCHAR(MAX); DECLARE @query AS NVARCHAR(MAX); -- Get distinct cities SELECT @cols = STRING_AGG(QUOTENAME(city), ',') WITHIN GROUP (ORDER BY city) FROM (SELECT DISTINCT city FROM players_location) AS Cities; ---print @cols; SET @query = ' SELECT * FROM ( SELECT name, city, ROW_NUMBER() OVER (PARTITION BY city ORDER BY name) AS rn FROM players_location ) AS SourceTable PIVOT ( MAX(name) FOR city IN (' + @cols + ') ) AS PivotTable ORDER BY rn; '; EXECUTE(@query);

[@suyash7450]

You are doing really and awesome job brother.... really helpful ...

[@ankitbansal6]

Glad to hear that

[@grim_rreaperr]

SELECT Bangalore, Mumbai, Delhi FROM ( SELECT * ,ROW_NUMBER() OVER(PARTITION BY city ORDER BY name ASC) AS rnk FROM players_location ) AS a PIVOT(MIN(name) FOR city in (Bangalore, Mumbai, Delhi)) AS b ORDER BY rnk;

[@parallelaxis]

Can you explain the concept of using max or min in case?

[@girishggirishg1083]

I really like the way you teach bro, keep up the good work.

[@ankitbansal6]

Glad to hear that!

[@rajunaik8803]

with cte as( select city,case when city='Bangalore' then name end as 'Bangalore', case when city='Mumbai' then name end as 'Mumbai', case when city='Delhi' then name end as 'Delhi', row_number() over(partition by city order by name asc) as rn from players_location ) select max(Bangalore) Bangalore,max(Mumbai) Mumbai, max(Delhi) Delhi from cte group by rn

[@Datapassenger_prashant]

here is my solution before watching the video.. my sol: Select max(case when city = 'mumbai' then name else null end ) as Mumbai , max(case when city = 'Delhi' then name else null end ) as Delhi ,max(case when city = 'Bangalore' then name else null end) as Bangalore from (Select *, row_number() over(partition by city order by name) as rn from players_location) A Group by rn

[@mayankpandey492]

Thanks Ankit for video, very well explained. And best part Is my name in table 😁👍

[@ankitbansal6]

😃

[@gauravgupta4783]

Bhai❤ You've made things much simple for me

[@ankitbansal6]

🙏

[@ujjwalvarshney3188]

create temp table uj as (select * ,row_number() over(partition by city order by name) as rk from players_location ); select * from uj select a.rk , max( case when a.city = 'Bangalore' then a.name else null end ) as "Bangalore" , max(case when b.city = 'Mumbai' then b.name else null end ) as "Mumbai", max(case when c.city = 'Delhi' then c.name else null end ) as "Delhi" from uj a left join uj b on a.rk= b.rk left join uj c on a.rk= c.rk group by 1 order by 1

[@Dhanushts-g7x]

select min(if(city="mumbai",name,null)) mumbai,min(if(city="delhi",name,null)) delhi , min(if(city="bangalore",name,null)) bangalore from( (select *,row_number() over(partition by city order by name) rn from players_location)) a group by rn (try this!)

[@sanjeevkumar-oc8wn]

Useful video Ankit!!

[@pandeyRaman]

u had hardcoded the city name so it is not flexible, is there any way to achieve flexibility?

[@mr.pingpong502]

select a.name as Mumbai,b.name as Banglore,c.name as Delhi from(select name,row_number() over(order by name) as rn from players_location where city='Mumbai')a left join ( select name,row_number() over(order by name) as rn from players_location where city='Bangalore')b on a.rn=b.rn left join ( select name,row_number() over(order by name) as rn from players_location where city='Delhi')c on a.rn=c.rn

[@shekharagarwal1004]

Again Kudos to you Ankit for this problem solving approach !!!

[@hairavyadav6579]

Today i learn new things

[@saktibiswal6445]

Thanks for the explanation!! 🙂🙂

[@parveen8122]

problem is similar to others just the difference is that we dont have ids on which we can group them into one row.Thanks

[@ankitbansal6]

True

[@sachindubey4315]

is there any other dynamic way to solve this problem? let suppose i have n number of cities then how once apply case when condition for this

[@kashmirshadows8150]

Thank you Ankit for the video… Quick Query, what if the city is list is too big …how can we make this clause dynamic in our query ?

[@ankitbansal6]

We will have write all the case whens. There is is pivot function that can be used as well

[@vikaskumar-qr5tj]

Nice way of Pivoting again some cool learning and Ankit i will request to make one compilation video of lot of questions on self join asked in interviews bcz it becomes really tricky when asked suddenly plz plz will be of great help in this playlist questions are there but still it will help a lot...

[@ankitbansal6]

Noted

[@Jaipreksha]

Excellent work brother

[@sunilshinde9897]

Hi sir, but what if we don't know city names , how to tackle such case.

[@AadiKesharwani-c6h]

SELECT [Mumbai],[Delhi],[Bangalore] from ( Select *, RANK() OVER (PARTITION BY CITY ORDER BY NAME) AS RNK FROM PLAYERS_LOCATION) as Q1 PIVOT( MAX(name) for city in ([Mumbai],[Delhi],[Bangalore]) ) as pvtTable

[@abhishek_grd]

Awesome ! Thank you

[@chilumugarinaresh5208]

Hi sir, In the place of city we need district and each city we had 10+ district so, can we follow same scenario that we seen in video.

[@ankitbansal6]

Yes

[@shivanshsawarn675]

Hi Ankit, Thanks a lot for the amazing videos. Just had one question, Any suggestion on the solution if we want the Location names to be dynamic, In case if we have 100 and 1000 of different location writing a case statement like this would be a tough call.

[@ankitbansal6]

You can use pivot function

[@ahsan_habib_sunny]

Is this also correct? I have used joined the table by row number with t1 as (select * , row_number() over(partition by city order by city) as rn from players_location), t2 as (select name as Bangalore, rn from t1 where city = 'Bangalore'), t3 as (select name as Mumbai, rn from t1 where city = 'Mumbai'), t4 as (select name as Delhi, rn from t1 where city = 'Delhi') select a.bangalore,b.mumbai,c.delhi from t2 as a left join t3 as b on a.rn = b.rn left join t4 as c on b.rn = c.rn ;

[@ankitbansal6]

This is good. Only thing is you will have to create as many cte as no of cities.

[@venkataramanakumar365]

this solution doesn't work if the "Right" side table has more values as you used left join , try this solution with adding more values with the city name 'Delhi' like ('ahsan', 'Delhi')... , run ur query and u will get to know that it is not the expected solution.

[@satyajitbiswal6162]

@@venkataramanakumar365 if will use full join then it will work both all cases

[@01kumarr]

really great explanation 🙂

[@ankitbansal6]

Thanks! 🙂

[@nirmalkumarr2235]

Superb 💯

[@ankitbansal6]

Thanks 🤗

[@vishalsonawane.8905]

not working below query Select max(case when city ='Bangalore' then name end) as Bangalore, max(case when city ='Mumbai' then name end) as Mumbai, max(case when city ='Delhi' then name end) as Delhi, row_number() over(partition by city order by name) as player_groups from players_location group by player_groups order by player_groups;

[@meenayegan6634]

select bangalore, mumbai,delhi from ( select * , ROW_NUMBER()over (partition by city order by name) as player_gps from players_location )x pivot ( max(name) for city in ([bangalore],[mumbai],[delhi]) )pt

[@Tusharchitrakar]

Can you explain how this can be done more dynamically without hard coding the city names into the code? Do we need to use dynamic sql in that case? I have been trying to figure out this for other situation too, please lmk.

[@Tusharchitrakar]

Also, i saw your reply regarding the use of the pivot function but MySQL doesn't support that.

[@ashpoghosh7645]

Hi Ankit, This time I couldn't find the schema and sample records in your description.

[@ankitbansal6]

Sorry I missed it. Just added now.

[@pranaydurvesula5891]

If we have more cities like 10 to 15 then do we need to enter all case when command for each city? Is there any other way.

[@ankitbansal6]

Yes you need to enter all 10 case when. As you are creating new columns 🙂

[@pranaydurvesula5891]

@@ankitbansal6 thank you ☺️

[@jatspower1]

What if we have 100 cities, difficult to manage case statement

[@ankitbansal6]

@@jatspower1 there is a pivot function. Check that out

[@anujgupta8686]

Good one... Need your advice these questions are asked in interviews and many interviewer want the result within mins even though sometime I am able to crack the logic but when some one told to write over teams chat it is little challenging reason being we need to think the output in mind and if they have group by or nested sub query it is easier to write in ssms rather than writing to teams chat. Any advice on that ?

[@ankitbansal6]

As you practice more it will become easier. Don't think about it too much it will come naturally

[@anujgupta8686]

@@ankitbansal6 yup daily doing.

[@sriharit.k8973]

I'm also facing the same issue. It is easier to work on the query on a RDBMS where we can slowly derive each part of the output to get to the final query properly but in interview I get stuck whenever there are multiple CTEs and sub queries involved as I have to derive the output of each CTE and subquery in my brain before proceeding.

[@syedkamran4121]

Please solve all problems of stratascratch

[@mukeshkumaryadav350]

Thank You.

[@1234abcd2139]

nice one ankit. Do you have some suggestion on what if the city is dynamic. Meaning more number of cities gets added or some cities gets deleted ?

[@ankitbansal6]

You will have to modify the query to add more. If deleted all the values in that col will be null.

[@vijaypalmanit]

Why you created rownum, this could have been achieved only using case statement

[@BengaluruGuy08]

Thanks...😊😊

[@anuragbawankar685]

Thanks a lot !

[@sharathraj4838]

Can the columnsbe created dynamically, like the number of cities are unknown, how can it be proceeded..!

[@ankitbansal6]

Not possible. Amy be done using procedure

[@sharathraj4838]

@@ankitbansal6 Thanks for answering the question. And I really like all the videos. You are doing a great job!

[@shivprasadshelgavkar4816]

@@sharathraj4838 may be you can achieve this by using dynamic SQL

[@uditprajapati5396]

Hi Ankit:I didn't not get the max or min concept

[@ankitbansal6]

We need a single row for each group key. Now in select i need to use aggregate functions otherwise it will fail.

[@tejkiran1836]

Hi Ankit, I have tried my query for this, with cte1 as (select name,city, row_number() over() as rn from players_location where city='Bangalore' order by name), cte2 as (select name,city, row_number() over() as rn from players_location where city='Mumbai' order by name), cte3 as (select name,city, row_number() over() as rn from players_location where city='Delhi' order by name) select cte1.name as Bangalore,cte2.name as Mumbai,cte3.name as Delhi from cte1 left join cte2 on cte1.rn=cte2.rn left join cte3 on cte2.rn=cte3.rn;

[@NishantKumar-oe9zd]

Hi Ankit Found one more solution 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 Please make your suggestion on above

[@AmanVerma-cu3lp]

How will you remove the rn column in the output?

[@NishantKumar-oe9zd]

@@AmanVerma-cu3lp Please find the below solution. select Mumbai,Bangalore,Delhi 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

[@AmanVerma-cu3lp]

@@NishantKumar-oe9zd Actually, I was working on a dynamic approach for this question. The query is similar, but I'm unable to remove the rn column. DECLARE @sql nvarchar(MAX) DECLARE @cols nvarchar(max) SELECT @cols = STUFF( (SELECT DISTINCT ', '+ QUOTENAME(city) FROM players_location FOR XML PATH('') ), 1, 1, '') SET @sql = '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 ('+@cols+') )AS PivotTable' EXEC(@sql)

[@adityabaha]

Ankit bhai thanks for this video. After watching this I want to ask your help on one more concept. If you can make a video on it please. Question: We all know that adding or removing a column in final group by clause gives different results, Can you please explain this in detail with more examples to clear this concept? Big thanks in advance 😊

[@ankitbansal6]

Sure 👍

[@nidhisingh4973]

Hi Ankit, I am unable to frame this query if I want o/p like City Name 1 Name2 Bangalore Mayank Rahul Mumbai Rohit Sachin Delhi Virat Null I can achieve this using Pivot. Can you plz help me with this using case statement

[@ankitkotnala196]

Hi Ankit: thanks for the video. Please help with the same problem using Pivot. Or please make a video specifically using PIVOT function. It would be very helpful.

[@ankitbansal6]

Ok

[@thestackingwomen1055]

select Bangalore, Mumbai, Delhi from ( SELECT Player_groups, Bangalore ,Mumbai ,Delhi FROM (select * , Row_Number() over(partition by city order by name ) As Player_groups from Players_location )Tab1 PIVOT ( Max(Name) FOR City IN (Bangalore,Mumbai,Delhi)) AS Tab2 ) As TT

[@lipunpatel5937]

Create Table #Crickter ( [Name] varchar(50), [City] varchar(50) ) Insert Into #Crickter([Name],[City]) Values('Sachin','Mumbai'), ('Virat','Delhi'), ('Rahul','Bangalore'), ('Rohit','Mumbai'), ('Mayank','Bangalore') select Name,City, RANK() OVER(Partition BY City Order By Name) As Ranks into #TempCrickter from #Crickter select * from #TempCrickter Select Mumbai,Delhi,Bangalore from #TempCrickter PIVOT( Max(Name) FOR[City] IN([Mumbai],[Delhi],[Bangalore]) ) As Crickter_Pivot

[@suprisahana]

What’s the use of row_number() function here? Will it not work without using it.

[@himanshupathak3090]

Hi Ankit, had one doubt here.. why are we using Max or Min function here?

[@zizu7755]

Use of MIN or MAX is required to eliminate NULL VALUES for each group. Watch video from 6:00

[@ashfakahamed9266]

Why cant we use the PIVOT() instead ?

[@ankitbansal6]

It's not exactly pivot

[@ashfakahamed9266]

@@ankitbansal6 Didn't get you. Can you pls eloborate ?

[@rocky6517]

Hi Ankit, if we have some personal doubt in sql problem how can I send you the problem to get it solved because I am stuck.

[@ankitbansal6]

Send me on [e-mail]

[@ashishkumarjha2587]

WITH cte AS (SELECT * ,row_number() OVER (PARTITION BY city ORDER BY name) AS player_grp FROM Players_location30) SELECT MIN(CASE WHEN city = "Bangalore" THEN name END) AS Bangalore ,MIN(CASE WHEN city = "Mumbai" THEN name END) AS Mumbai ,MIN(CASE WHEN city = "Delhi" THEN name END) AS Delhi FROM cte GROUP BY player_grp ORDER BY player_grp

[@vijaypalmanit]

I feel there is no need to create row_num, this question can be solved using case only

[@ankitbansal6]

Try it 😊

[@paprfushorts]

But pivot is easier then this case statement as this is a very long process

[@greatromulus3028]

Hi Ankit thanks for this great video. I have a question if the table is dynamic number of cities and city names change everyday. Can we write a sql query adapting different cities ? For example ; day 1 table; 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'); day 2 table; 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',),('Ankit','Tokyo',); day 3 table; create table players_location ( name varchar(20), city varchar(20) ); delete from players_location; insert into players_location values ('Sachin','London'),('Virat','Delhi') , ('Rahul','Bangalore'),('Rohit','London'),('Mayank','Bangalore',),('Ankit','Tokyo',);

[@parth_pm16]

+1

[@varaprasadpurum389]

Hi Ankit, Firstly I would thankful the sharing this sql challenges Here is my solution WITH CT1 as (SELECT CITY, CASE WHEN CITY='Bangalore' THEN NAME END as Bangalore, CASE WHEN CITY='Mumbai' THEN NAME END as Mumbai, CASE WHEN CITY='Delhi' THEN NAME END as Delhi, row_number() over(partition by city order by name asc) as rn FROM players_location GROUP by CITY, NAME ) SELECT max(Bangalore),max(Mumbai),max(Delhi) FROM CT1 group by rn

[@ankitbansal6]

Looks good. Thanks for posting 👏

[@vaibhavverma1340]

My Solution :- with cte as (select *, ROW_NUMBER() over (partition by city order by name)rn from players_location) select MIN(case city when 'Bangalore' then name end)Bangalore, MIN(case city when 'Mumbai' then name end)Delhi, MIN(case city when 'Delhi' then name end)Mumbai from cte group by rn Thanks for sharing:)

[@ankitbansal6]

Perfect

[@atulk9122]

Wow wow

[@sonurohini6764]

Ankit few videos are blurred. See of you can do something on that. That would be helpful

[@ankitbansal6]

Change the video quality from KZbin settings

[@aravindsastry8895]

My solution WITH CTE AS ( SELECT CASE WHEN CITY = 'Mumbai' THEN NAME ELSE NULL END AS Mumbai, CASE WHEN CITY = 'Delhi' THEN NAME ELSE NULL END AS Delhi, CASE WHEN CITY = 'Bangalore' THEN NAME ELSE NULL END AS Bangalore FROM players_location ) ,MUMBAI AS ( SELECT Mumbai, row_number() over(order by mumbai) AS RN FROM CTE WHERE Mumbai is not null ), Delhi AS ( SELECT Delhi, row_number() over(order by Delhi) AS RN FROM CTE WHERE Delhi is not null ), Bangalore AS ( SELECT Bangalore, row_number() over(order by Bangalore) AS RN FROM CTE WHERE Bangalore is not null ) SELECT Bangalore,Mumbai,Delhi FROM MUMBAI A FULL JOIN Delhi B ON A.RN = B.RN FULL JOIN Bangalore C ON A.RN = C.RN ORDER BY A.RN

[@TheIllavarasi]

Hi Ankit, thanks for the videos, you explain it in a simple way which is easy to catch. I was going through sql advanced certification program on hackerrank and could not solve crypto currency transaction problem. Request you to help on the same.

[@ankitbansal6]

Link please

[@AdityaBansal095]

Hey Ankit This is my solution declare @query nvarchar(max) declare @query1 nvarchar(max) set @query=' select [Bangalore],[Mumbai],[Delhi] from (select * from #TEMP where rnk=1 )a pivot( max(name) for city in ([Bangalore],[Mumbai],[Delhi]))x' --exec(@query) set @query1=' select [Bangalore],[Mumbai],[Delhi] from (select * from #TEMP where rnk=2 )a pivot( max(name) for city in ([Bangalore],[Mumbai],[Delhi]))x' EXEC(@query+' UNION ALL ' +@query1)

[@sumantamandal353]

WITH CTE AS ( SELECT LISTAGG(CASE WHEN CITY = 'BANGALORE' THEN PLAYER_NAME END,',') AS BANGALORE, LISTAGG(CASE WHEN CITY = 'DELHI' THEN PLAYER_NAME END,',') AS DELHI, LISTAGG(CASE WHEN CITY = 'MUMBAI' THEN PLAYER_NAME END,',') AS MUMBAI FROM PLAYER ) SELECT REGEXP_SUBSTR(BANGALORE,'[^,]+',1,level) AS BANGALORE, REGEXP_SUBSTR(DELHI,'[^,]+',1,level) AS DELHI, REGEXP_SUBSTR(MUMBAI,'[^,]+',1,level) AS MUMBAI FROM CTE CONNECT BY REGEXP_SUBSTR(BANGALORE,'[^,]+',1,level) IS NOT NULL ;