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

2 жыл бұрын

😊

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;

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

@ankitbansal6

2 жыл бұрын

Right

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

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

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!

Useful video Ankit!!

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

@ankitbansal6

8 ай бұрын

Well done!

Awesome ! Thank you

Thanks for the explanation!! 🙂🙂

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

@rishav144

Жыл бұрын

true

Excellent work brother

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

@ankitbansal6

Жыл бұрын

Glad to hear that!

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

Bhai❤ You've made things much simple for me

@ankitbansal6

2 жыл бұрын

🙏

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

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

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

@ankitbansal6

6 ай бұрын

Glad to hear that

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

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

2 жыл бұрын

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

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

2 жыл бұрын

Looks good. Thanks for posting

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

@ankitbansal6

2 жыл бұрын

😃

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

2 жыл бұрын

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

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

2 жыл бұрын

Sure 👍

Thanks a lot !

Thanks...😊😊

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

really great explanation 🙂

@ankitbansal6

Жыл бұрын

Thanks! 🙂

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

@dattatreyakulkarni541

8 күн бұрын

Yes, we we should add city as dynamically

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

2 жыл бұрын

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

@anujgupta8686

2 жыл бұрын

@@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.

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

2 жыл бұрын

Ok

@thestackingwomen1055

2 жыл бұрын

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

Superb 💯

@ankitbansal6

Жыл бұрын

Thanks 🤗

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

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

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 ;

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

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

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

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

2 жыл бұрын

Yes

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

2 жыл бұрын

Thanks for posting 👏

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);

Wow wow

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

8 ай бұрын

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

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

@ankitbansal6

2 жыл бұрын

Sorry I missed it. Just added now.

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

2 жыл бұрын

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

Please solve all problems of stratascratch

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

2 жыл бұрын

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

@pranaydurvesula5891

2 жыл бұрын

@@ankitbansal6 thank you ☺️

@jatspower1

2 жыл бұрын

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

@ankitbansal6

2 жыл бұрын

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

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

@zizu7755

5 ай бұрын

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

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

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

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

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

2 жыл бұрын

Send me on ankitbansal1988@gmail.com

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

@ankitbansal6

2 жыл бұрын

Not possible. Amy be done using procedure

@sharathraj4838

2 жыл бұрын

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

@shivprasadshelgavkar4816

2 жыл бұрын

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

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;

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;

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

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

@ankitbansal6

2 жыл бұрын

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

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)

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

Why cant we use the PIVOT() instead ?

@ankitbansal6

Жыл бұрын

It's not exactly pivot

@ashfakahamed9266

Жыл бұрын

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

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

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!)

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

@ankitbansal6

2 жыл бұрын

Try it 😊

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

2 жыл бұрын

Link please

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

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

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

10 ай бұрын

+1

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 👏

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;

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

@ankitbansal6

Ай бұрын

Change the video quality from KZread settings

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

2 жыл бұрын

Perfect

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

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 ;