Man you might don't know how much you are helping other people. True gem

@ankitbansal6

2 жыл бұрын

🙏

Man you are an amazing person helping a lot of people

Thank you for your video. I have an exact requirement and your video makes it very clear with a proper description and makes it easy to understand. Thank you so much

@ankitbansal6

2 жыл бұрын

Glad it helped you 😊

Really a great concept, explained very well.

Practising from your videos before my interview. Thanks for the videos.

@ankitbansal6

Жыл бұрын

Best of luck

Great video! Excellent step-by-step explanation. Thank you.

@ankitbansal6

2 жыл бұрын

🙏

this is what I was searching for.. thanks man

I got a query related to this, thank you now i got some clarity.

Thanks Ankit for your time on this video 😊 appreciate all your effort

I was asked this question in an interview (US based Health insurance company) but I only knew how to solve it using Pivot function and could not answer it. I wish if I had found this video sooner!

This was an awesome tutorial! Very informative without the extra puff. Thank you so much!

@ankitbansal6

7 ай бұрын

Glad it was helpful!

keep doing what you are doing really it is very helpful.

Sir you are brilliant, so much easier then using SQL pivot operator. Thank you kindly.

@ankitbansal6

Жыл бұрын

Happy to help

🎯 Key Takeaways for quick navigation: 00:14 📊 Learn how to convert rows to columns and vice versa in SQL without using pivot functions. 01:25 🧮 Use SQL case statements to pivot data from rows to columns for specific components. 04:27 🧾 Practice using case statements with sum for efficient data pivoting in SQL. 05:28 🔄 Unpivot data by creating a new table using SELECT INTO and then using UNION to achieve the desired format. 08:18 📚 Mastering case when with sum is crucial for handling data transformations, and it's a valuable skill for interviews and job tasks. Made with HARPA AI

Hi ankit, thank you so much for simplifying the concepts. It would be great if you could make similar videos using the sum and case concept solving real interview questions or problems

@ankitbansal6

Жыл бұрын

I have several videos on my channel using that concept. 😊

Very much helpful video. Ankit sir please upload such more complex query video

Thank you very much, Sir. The same question I have been asked in the interview. And because I had already watched this video, I could be able to solve this in the interview.

@ankitbansal6

9 ай бұрын

Excellent

it's hard to see so so clear and easy understanding video. thank you

@ankitbansal6

Жыл бұрын

Glad it was helpful!

Informative content ❣

Thanks Ankit for the video, we have to use pivot function using pandas for subsequent ml work, but now we can do the data transformation directly in SQL and post which we can directly train the ml model

@ankitbansal6

Жыл бұрын

Yes, you are right

Your videos has golden content! Just wowwwww!

@ankitbansal6

2 жыл бұрын

🙏🙏

@Ankit - Awesome very well explained !

Thanks for explanation. It was nice

It's really awesome video. I have learnt some great insights from this video

@ankitbansal6

Жыл бұрын

Glad to hear that!

Nice video! Thank you.

Well explained video!

Good one ankit, I am watching daily 1-3 video from your channel to prepare for faang

@ankitbansal6

2 жыл бұрын

Awesome

I have been working with SQL for a decade now. Now only I know that we can use 'val' for producing result instead of the field name itself. True Genius.

@ankitbansal6

20 күн бұрын

val is a column in the table 😊

@jececdept.9548

9 күн бұрын

​@@ankitbansal6 pls suggest how to study from so many videos..is there any pdf of these available.?

@ankitbansal6

9 күн бұрын

@@jececdept.9548 just solve 1 question at a time everyday

Hello ankit sir, Truly blessed to come across your superb videos....thank you so much sir and please keep helping is this way by more of ur interview questions on sql, pythons

@ankitbansal6

7 ай бұрын

Keep watching

This was great and well explained. Thanks

@ankitbansal6

4 ай бұрын

Glad you enjoyed it!

Thankyou so much for this video

This is very helpful! Thanks

@ankitbansal6

11 ай бұрын

Glad it was helpful!

Thanks very well explained...!

@ankitbansal6

2 жыл бұрын

Glad it was helpful!

Great work Ankit so helpful thank you.

@ankitbansal6

Жыл бұрын

Glad it helped

Thanks a lot sir for explaining in detail.

@ankitbansal6

11 ай бұрын

You are most welcome

Really good explanation, great job :)

@ankitbansal6

2 жыл бұрын

Thank you 😊

Hi Ankit, very crisply explained. Can you explain how each row gets created in both the cases.I mean execution of the query. Thanks in advance!!

very useful 👌👌👍👍

So fantastic. Thank you so much. :)

@ankitbansal6

Жыл бұрын

You are so welcome!

Thank you, this help me a lot.

@ankitbansal6

Жыл бұрын

Glad it helped!

Thank you valuable share

Thank you for your efforts : )

@ankitbansal6

11 ай бұрын

My pleasure!

hello sir. thank you for sharing these concepts.its really helpful.sir im an aspiring data analyst and i really want to become good in sql.i know the basics and intermediate concepts.from where should i practice the hard concepts like this which you are teaching?once again thank u.

Hi Ankit, Thanks for creating this video . It was really helpful . Can you please create more examples around this.

@ankitbansal6

2 жыл бұрын

Sure

Thank you so much

True superhero👍

Hello Ankit, I bought multiple courses on SQL but finally started to understand by watching your videos. Thank you so much. I want to know where can I get more use cases to practice for case statements using sum and other aggregate functions.

@ankitbansal6

Жыл бұрын

Check out the complex SQL problems playlist

Well explained... Please make some videos on path to join Amazon as a Business Intelligence Engineer or Data Engineer.

@ankitbansal6

2 жыл бұрын

Sure.

Thank you so much sir for your help. 👍👍🙏

@ankitbansal6

10 ай бұрын

Keep watching 💪

Great session 👍

@ankitbansal6

Жыл бұрын

Glad you enjoyed it

thank you so much

Thanks friend very helpful

@ankitbansal6

2 жыл бұрын

Welcome

Nice Video, thank you. I have a doubt. Is there any way that we can dynamically create these columns during pivoting instead of explicitly typing the column values in each sum(case) statement? If we have more unique values and more rows, it might be useful if possible.

@ankitbansal6

2 жыл бұрын

There is pivot function to do that. I will make a separate video.

Very nicely explained

@ankitbansal6

10 ай бұрын

Keep watching😊

Thank you so much ! 🙂

@ankitbansal6

Жыл бұрын

You're welcome 😊

i have also tried this approach using window functions - select emp_id, salary, hike_percent, bonus from ( select emp_id, val as salary, lead(val) over a as hike_percent, lead(val,2) over a as bonus, row_number() over a as rn from emp_compensation window a as (partition by emp_id order by salary_component_type desc) ) t where rn=1 Thank you

Thanks for sharing much value , a small request if you can make such more videos god bless you

@ankitbansal6

Жыл бұрын

Sure 😊

@alex45688

Жыл бұрын

@@ankitbansal6 People like you are a great help to the society and for new learners, we pray many people support this channel Will inform other people also to join this channel

Hi Ankit I am learning a lot from you. Thank you, Thank you again. Please clear my doubt. Pasting my query below. I tried this method. but following both this method and aggregating both ways coming same output select emp_id, sum(case when salary_component_type = 'salary' then val end) as salary, sum(case when salary_component_type = 'bonus' then val end) as bonus, sum(case when salary_component_type = 'hike_percent' then val end) as hike_percent from emp_compensation group by emp_id; select emp_id, max(case when salary_component_type = 'salary' then val end) as salary, max(case when salary_component_type = 'bonus' then val end) as bonus, max(case when salary_component_type = 'hike_percent' then val end) as hike_percent from emp_compensation group by emp_id; how???

@ankitbansal6

Ай бұрын

There is only one not null value in each group. So max min avg all same ..

@Ashu23200

Ай бұрын

@@ankitbansal6 ok

Hi Ankit, Your videos are helpful to learn SQL in depth, do you have any github repository where you have hosted all your YT questions and solutions ? Or something in text/ blog format ?

@ankitbansal6

Жыл бұрын

I haven't done that yet. Planning that.

Great video

@ankitbansal6

2 жыл бұрын

Glad you enjoyed it

Hi Ankit, if we have other salary component we have to alter the query . Can we dynamically generate the columns(for each Salary component)without using the pivot function.

Very well explained. I just have one question which may sound very basic. How to distinguish between pivoting and unpivoting? i always get confused which one is what

@ankitbansal6

2 жыл бұрын

Row to column is pivot and reverse in unpivot.

super sir make a more interview quations.....

Thanks a lot Ankit it was very helpful. Just one question in case we have multiple column like let's say 10-15 then we need to write 10-15 case statement from row to column and 10-15 union all in case od column to row or do we have any other approach

@ankitbansal6

Жыл бұрын

Alternatively you can use pivot and unpivot functions.

excellent

Hello Ankit, firstly congratulations for hitting 1lakh subscribers on youtube and gratitude for the resourceful videos. I have one doubt while trying to unpivot the table i am encountering the error msg Undeclared variable : emp_compensation_pivot , how to remove that error. Pls help. Thanks in advance.

Thank you Ankit for awesome explanation. Is there another video to practice this topic?

@ankitbansal6

Жыл бұрын

There are many in this playlist Complex SQL Questions for Interview Preparation: kzread.info/head/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

Thanks a lot Baha’i, very helpful video. I tried this in report which has 200 columns to unpivot and 5 inner joins, finally the script has 2300 lines now (each union has 15 lines). I have used inner joins on each union. Is it the a way to refine the script more!

Thank you . What if I add one more type called Gender that has "Male" or "Female" - we cannot do SUM or any numerical aggregate like MIN/MAX in that case - so how do we solve that?

Well explained , just a request Please make a video on how to upload DB tables on this Software , it would be a great help from ur end !!

@ankitbansal6

2 жыл бұрын

You mean you want to load data from files in your system to SQL server right ?

@VARISHROCKS

2 жыл бұрын

@@ankitbansal6 YES

Hi Ankit great video. Just one doubt can sum function work on string values in the case statement? Example just adding another columns as name and pan no.

@ankitbansal6

2 жыл бұрын

Yes it works on string.

@kartiks792

2 жыл бұрын

@@ankitbansal6 thank you for the clarification. 😊

This solved my potential career-ending problem, thanks a lot brother. How can I perform high performance on this when I am working on big data?

@ankitbansal6

2 жыл бұрын

It's optimised technique only.

Is 'select into' syntax for creating new table worked in MySQL ? I got error

awesome vide

@ankitbansal6

10 ай бұрын

Glad you enjoyed it

The same query how can we perform if the value has non numeric values.

How to pivot If all my columns having date. If sum will work in that case?

hi bro actually i tried the same but while doing unpivoting .... "select emp_id, 'salary' as salary_component_type, salary as val from temp" i got an error code 1054: unknown column salary in field list (where i actually want 10000 as output in val column). can u help me with this.

Hi Ankit, if possible pls create video on how to do pivoting dynamically. If new entries comes in future it will automatically comes from rows to columns

@ankitbansal6

Жыл бұрын

Okay

@ankit How to dynamically pick pivot rows to columns without using pivot function?

I am a big fan if you Ankit 💓

@ankitbansal6

Жыл бұрын

Thank you 😊

Hi Ankit, i am trying to run the below query in MySQL but getting error like undeclared variable at into emp_compensation_pivot Select emp_id ,SUM(case when salary_component_type = 'salary' then val end) as Salary ,SUM(case WHEN salary_component_type = 'bonus' then val end) as Bonus ,SUM(case when salary_component_type = 'hike_percent' then val end) as hike_percent into emp_compensation_pivot from emp_compensation group by emp_id; Can you please guide me

@ankitbansal6

Жыл бұрын

Use create table as instead of into

@yatinshekhar787

Жыл бұрын

@@ankitbansal6 sir used create table statement also but it is not working is their any other alternative?

Hi Ankit, Nice video, thanks for this one. Is there a video, to convert rows to columns with column name dynamically, instead of explicit mention in the case statement and pivot function. Note: Sybase doesn't have Pivot function.

@ankitbansal6

2 жыл бұрын

Sure will look into it 🙂

@divyav3827

2 жыл бұрын

Thank you! Looking forward to your video on it

What if there are 2 columns example val and expense_amount. So i want 2 colums from 1 row. Salary_val and salary_expense_amount. How to do that?

Hi Ankit, This was very informative. I had the a bit similar usecase in my work but the second column will have 1000s uniques and third columns have respective values. I was able to solve it using Python but the transformation was slow. Is there a way to extend this solution with or without PIVOT for 1000s of unique records ?

@ankitbansal6

2 жыл бұрын

Which means you will have 1000s of column in your o/p?

@rakeshchaudhary8255

2 жыл бұрын

@@ankitbansal6 Correct. In short we need to produce the exact same logic for large dataset. how to scale up this solution for 1000 unique values of second column of the original dataset ?

@ankitbansal6

2 жыл бұрын

@@rakeshchaudhary8255 there is a pivot function. I will make a video on it.

@SharukhKhan-pu5sd

Жыл бұрын

@@ankitbansal6 Will search for this .I would like to understand how this can be implemented.

This method is dependent on number of columns. How to make this dynamic like pivot. Pivot automatically takes new column into account whereas this method will require to change query to add every new column. Please help.

Hi i have try this example in DB but this is not working it show me error when i use case statement and using then val showing invalid column name please suggest me.

can this process be used another query where numbers are not involved for pivot

Thanks for your video, but what if I want to unpivot 35 different months columns into row based and 35 quantity columns for the corresponding month into row based. This method is not useful fod that, also the unpivot function is not efficient here, query processing times is going more then an hour, could you please suggest any approach for this

@ankitbansal6

2 жыл бұрын

You have only 2 options. Either use pivot unpivot functions or do like in video.

This is awesome!! Would this syntax work in mysql too?

@ankitbansal6

Жыл бұрын

Yes it will

Hi Ankit, thank you sharing such a wonderful tip. this solves purpose.. But i have text value in "Val" column. when i use sum function, it shows error invalid digit value 'L'. any idea to group text values

@ankitbansal6

3 ай бұрын

You can use string agg function ..or can use min , max

Please make a video on using Pivot functions and merge statement

@ankitbansal6

2 жыл бұрын

Sure.

Hello Ankit! This was really helpful, thank you so much for explaining it step by step. I came across a similar problem but have quite a few doubts, could you please help me understand how the same concept can be applied for the below table: NAME PROFESSION Samantha Doctor Julia Actor Maria Actor Meera Singer Ashley Professor Ketty Professor Christeen Professor Jane Actor Jenny Doctor Priya Singer Here is the query that I used: select case when profession = 'doctor' then person_name end as 'doctor', case when profession = 'actor' then person_name end as 'actor', case when profession = 'professor' then person_name end as 'professor', case when profession = 'singer' then person_name end as 'singer' from occupation How do I exclude the nulls is this case?

@ankitbansal6

2 жыл бұрын

You just need to create one case statement with multiple when ..that will do

@vigneshnagaraj7137

Жыл бұрын

Hi Hiya, Please post your solution . It will be helpful

@amazhobner

Жыл бұрын

I guess this is a hackerrank question, you can just group by and take min/max.

@chaitanyagn

Жыл бұрын

Hello Hiya I tried to solve the same problem using the above mentioned method. The above example has emp-id, we can apply GROUP BY on this emp-id and get the desired result(remove the null values). However in the problem, we dont have any such column to perform GROUP BY on and eliminate the nulls. To overcome this we create a new column ‘grouper’. What grouper does is, it ranks the candidates for each profession. For ex: If there are 4 doctors, we give a rank to each doctor. Similarly for other professions. Then we perform GROUP BY on grouper column and get the desired result. Here’s the piece of query I have used. SET @r1=0, @r2=0, @r3=0, @r4=0; WITH tb1 AS (SELECT CASE WHEN Occupation = 'Doctor' THEN Name END AS Doctor, CASE WHEN Occupation = 'Professor' THEN Name END AS Professor, CASE WHEN Occupation = 'Singer' THEN Name END AS Singer, CASE WHEN Occupation = 'Actor' THEN Name END AS Actor, CASE WHEN Occupation = 'Doctor' THEN (@r1:=@r1+1) WHEN Occupation = 'Professor' THEN (@r2:=@r2+1) WHEN Occupation = 'Singer' THEN (@r3:=@r3+1) WHEN Occupation = 'Actor' THEN (@r4:=@r4+1) END AS grouper FROM OCCUPATIONS ORDER BY Name) SELECT MIN(Doctor) AS Doctor, MIN(Professor) AS Professor, MIN(Singer) AS Singer, MIN(Actor) AS Actor FROM tb1 GROUP BY grouper; Hope this helps !

Hi Ankit, Really love your content, my SQL skills are getting better because of this series. Thanks a lot. Can you please tell a good source to practice SQL online with questions of these standards

@ankitbansal6

2 жыл бұрын

Good to know that 😊 I think if you solve all my videos. It is good enough 🙂

@naveennvnkumar4615

2 жыл бұрын

@@ankitbansal6 Thanks, will do that

I could not understand sir we can do it by pivot function right ? can u please explain ?

what happend when someone enter the new salary component type the query set was only the exitsting data when someone enter the new salary component type so every time i change the query it's not good please help me this like whenever some add new salary component type how to automatically add val in query

@ankitbansal6

Жыл бұрын

You need to modify the query. Or wrote a dynamic SQL using pl SQL or t SQL

If we don't use SUM function, salary is automatically getting aggregated, but bonus and hike_percent are giving NULL. Why ?

Hi Ankit, first of all thanks for this playlist. I wanted to ask you that what if there is no emp_id col for grouping and the val column has different 'string' values? then how are we supposed to do it since we cannot use sum

@ankitbansal6

Жыл бұрын

But you should have some common key to identify the relationship

@TheSAKiMan

Жыл бұрын

@@ankitbansal6 Ashley Professor Samantha Actor Julia Doctor Britney Professor Maria Professor Meera Professor Priya Doctor Priyanka Professor Jennifer Actor Ketty Actor Belvet Professor Naomi Professor Jane Singer Jenny Singer Kristeen Singer Christeen Singer Eve Actor Aamina Doctor

@TheSAKiMan

Жыл бұрын

This is the table from hackerrank, and we need to pivot according to the occupations of everyone and there'll be four columns.

@ankitbansal6

Жыл бұрын

@@TheSAKiMan here you go kzread.info/dash/bejne/l2F9m7Wxkq_RqZs.html

@TheSAKiMan

Жыл бұрын

Thanks a lot!!

Hi Ankit - Thanks for Video. How we can use this if we have char/varchar instead of integers in the table

@ankitbansal6

Жыл бұрын

Use Max min

Does any one know how to solve this problem if we have strings instead of integers. I can't add string right what is the alternative?

Are case statements faster than pivot ?

@ankitbansal6

3 ай бұрын

I would say you have the flexibility to give multiple case conditions based on the requirements.

@LivenLove

3 ай бұрын

Thanks for replying so promptly. Do you have or planning to release any spark project based content?