SQL Convert Rows to Columns and Columns to Rows without using Pivot Functions

No video

SQL Convert Rows to Columns and Columns to Rows without using Pivot Functions

Рет қаралды 116,151

2 жыл бұрын

In this video we will discuss how to convert rows to columns and vice versa without using pivot functions. Below is the script to create table and data:
create table emp_compensation (
emp_id int,
salary_component_type varchar(20),
val int
);
insert into emp_compensation
values (1,'salary',10000),(1,'bonus',5000),(1,'hike_percent',10)
, (2,'salary',15000),(2,'bonus',7000),(2,'hike_percent',8)
, (3,'salary',12000),(3,'bonus',6000),(3,'hike_percent',7);
select * from emp_compensation;

Пікірлер: 258

@ankitbansal6 28 күн бұрын

Master the art of SQL with my zero to hero SQL for data analytics course. www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english

@as-youtuber6215 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

🙏

@ankush_in_sync5998 10 күн бұрын

One of the unique thing Ankit sir does is giving the table script in the description. By this we can copy the script and practice. Thank you.

@ankitbansal6 10 күн бұрын

Cheers 🥳

@sujaa1000 16 сағат бұрын

One of the best channels, thank you very much Ankit!

@ankitbansal6 14 сағат бұрын

My pleasure

@sanchitvaid7017 Жыл бұрын

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!

@RAAMASHAAMYC Ай бұрын

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 Ай бұрын

val is a column in the table 😊

@jececdept.9548 Ай бұрын

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

@ankitbansal6 Ай бұрын

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

@ratiranjannayak5712 2 жыл бұрын

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 😊

@bhooshan25 Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Best of luck

@CricketLivejavwad 19 күн бұрын

Thank you for the awesome video.I tried to solve this by myself after getting hint by this video and after struggling finally I was able to solve this problem and one more thing I want to tell you this concept is used in my job role also. Thanks again sir :)

@ankitbansal6 18 күн бұрын

Glad it helped!

@CricketLivejavwad 18 күн бұрын

Thank you sir @@ankitbansal6

@lokeshvaishnav218 Жыл бұрын

Man you are an amazing person helping a lot of people

@Ashu23200 2 ай бұрын

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 2 ай бұрын

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

@Ashu23200 2 ай бұрын

@@ankitbansal6 ok

@girishggirishg1083 Жыл бұрын

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

@SachinKumarHS 3 ай бұрын

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

@ashutoshpandey1452 Жыл бұрын

this is what I was searching for.. thanks man

@shoaibshaikh8909 Жыл бұрын

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

@brianducharme3418 Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Happy to help

@Exceltech810 Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Glad it was helpful!

@user-mn4og3ql4x 10 ай бұрын

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 10 ай бұрын

Excellent

@tupaiadhikari Жыл бұрын

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

@shubhamgoyal3358 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Awesome

@nigarmutallimova8461 8 ай бұрын

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

@ankitbansal6 8 ай бұрын

Glad it was helpful!

@karthikvb1293 2 жыл бұрын

Really a great concept, explained very well.

@sharu164 Жыл бұрын

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

@jaydeeppatidar4189 2 жыл бұрын

Your videos has golden content! Just wowwwww!

@ankitbansal6 2 жыл бұрын

🙏🙏

@adreamer9999 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

🙏

@ankushjain4128 7 ай бұрын

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

@ujjwalvarshney3188 Жыл бұрын

select a.emp_id ,a. val as salary , b.val as bonus ,c.val as hike_percent from emp_compensation a join emp_compensation b on a.emp_id = b.emp_id join emp_compensation c on a.emp_id = c.emp_id where a.salary_component_type = 'salary' and b.salary_component_type ='bonus' and c.salary_component_type = 'hike_percent' order by 1

@saurabhkhare3315 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Sure.

@MotivationToMoveForward Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Glad to hear that!

@kawshalchettri6614 Жыл бұрын

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

@thedatakid1270 2 жыл бұрын

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

@shilpibiswas3110 8 ай бұрын

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 8 ай бұрын

Keep watching

@georgehall2882 5 ай бұрын

This was great and well explained. Thanks

@ankitbansal6 5 ай бұрын

Glad you enjoyed it!

@raghuramm3297 8 ай бұрын

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.

@udaykumar-dl6ux Жыл бұрын

Thanks a lot sir for explaining in detail.

@ankitbansal6 Жыл бұрын

You are most welcome

@eel3720 Жыл бұрын

Great work Ankit so helpful thank you.

@ankitbansal6 Жыл бұрын

Glad it helped

@aravindsatheesh4086 2 жыл бұрын

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.

@monasanthosh9208 3 ай бұрын

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 Group by Emp_id Order by EMp_id;

@rakeshgopidi4066 Жыл бұрын

@Ankit - Awesome very well explained !

@suketshah7697 2 жыл бұрын

Thanks very well explained...!

@ankitbansal6 2 жыл бұрын

Glad it was helpful!

@taniyasaini6830 2 жыл бұрын

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.

@ankithashetty9133 21 күн бұрын

Incase if the rows are way to high how do we dynamically bring them as column names, is there a way to do it? It's not possible to give names individually . In my case I should bring dates as column names from the row.

@gritmanish Жыл бұрын

Thanks for explanation. It was nice

@mohit231 2 жыл бұрын

Great video

@ankitbansal6 2 жыл бұрын

Glad you enjoyed it

@garimabatra2658 2 ай бұрын

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.

@mukeshkumaryadav350 6 күн бұрын

Thank you!

@milindzuge906 2 жыл бұрын

Informative content ❣

@poorvaagnihotri7104 17 күн бұрын

Hi Ankit, Thanks for this explaination. I was asked below question in LatentView Analytics Interview , can you share a optimal solution for that Input:- class Sub A Maths B English A Social B Chemistry A Physics B Statistics A Biology B Maths A Science B Social Result: Class Sub1 Sub2 Sub3 Sub4 Sub5 A Science Physics Social Maths Biology B English Social Maths Statistics Chemistry

@ankitbansal6 17 күн бұрын

You can use a similar case when statements as explained in video. Just use max or min along with case when.

@alex45688 Жыл бұрын

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

@NaveenKumar-ig6gn Жыл бұрын

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?

@niveditasingh1176 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Sure

@mallikarjunpatil7553 Жыл бұрын

Great session 👍

@ankitbansal6 Жыл бұрын

Glad you enjoyed it

@ashishdukare1313 Жыл бұрын

Thank you for your efforts : )

@ankitbansal6 Жыл бұрын

My pleasure!

@ramakumarguntamadugu1299 5 ай бұрын

very useful 👌👌👍👍

@MrKingoverall 11 ай бұрын

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

@ankitbansal6 11 ай бұрын

Keep watching 💪

@subhojeetlearning 11 ай бұрын

Very nicely explained

@ankitbansal6 11 ай бұрын

Keep watching😊

@JosueLopezXIX 3 ай бұрын

Nice video! Thank you.

@metricsview33 8 ай бұрын

excellent

@desflan06 Жыл бұрын

Thank you, this help me a lot.

@ankitbansal6 Жыл бұрын

Glad it helped!

@mohit231 2 жыл бұрын

Please make a video on using Pivot functions and merge statement

@ankitbansal6 2 жыл бұрын

Sure.

@tomthomas6985 Жыл бұрын

Using PIVOT, we can solve it by: select emp_id,salary,bonus,hike_percent from (select emp_id,salary_component_type,val from emp_compensation) p PIVOT( max(val) for salary_component_type in ([salary],[bonus],[hike_percent]) )as pivot_table

@adityaf17 Жыл бұрын

This is very helpful! Thanks

@ankitbansal6 Жыл бұрын

Glad it was helpful!

@shubhiverma2571 Жыл бұрын

Thankyou so much for this video

@KukuG28 4 ай бұрын

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.

@hiyaborah9167 2 жыл бұрын

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 !

@emiliogarza6446 2 жыл бұрын

Really good explanation, great job :)

@ankitbansal6 2 жыл бұрын

Thank you 😊

@rashmidutta7151 2 жыл бұрын

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

@scrt1111 Жыл бұрын

Thank you valuable share

@toshitachakraborty4184 Жыл бұрын

I am a big fan if you Ankit 💓

@ankitbansal6 Жыл бұрын

Thank you 😊

@pavitrashailaja850 2 жыл бұрын

Well explained video!

@shahmaarbaba Жыл бұрын

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

@vinipowerbi Жыл бұрын

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: kzfaq.info/sun/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

@harshitaggarwal3554 Жыл бұрын

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.

@kanchans3556 5 ай бұрын

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

@kundankumar5520 Жыл бұрын

True superhero👍

@NaveenKumar-fq4sb Жыл бұрын

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

@rajakrishnan8561 4 ай бұрын

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 4 ай бұрын

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

@TheEJAZ88 Ай бұрын

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

@snipezu805 2 жыл бұрын

Thanks friend very helpful

@ankitbansal6 2 жыл бұрын

Welcome

@rohanp5421 7 ай бұрын

This is not dynamic. This will only work when we know data fully.

@seelamrk Жыл бұрын

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?

@chronoray8786 Жыл бұрын

So fantastic. Thank you so much. :)

@ankitbansal6 Жыл бұрын

You are so welcome!

@navkarwebstudio7490 10 ай бұрын

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

@sandycool1822 Жыл бұрын

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

@surojeetghosh8389 Жыл бұрын

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.

@rutujakelkar2895 Жыл бұрын

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.

@manishkadam6724 4 ай бұрын

Thank you so much

@idwtv534 6 ай бұрын

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?

@hariarayakeel 2 жыл бұрын

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!

@NaturalPro100 10 ай бұрын

awesome vide

@ankitbansal6 10 ай бұрын

Glad you enjoyed it

@viveknegi7253 Жыл бұрын

My Answer: with pviot as (select emp_id,sum(case when salary_component_type='salary' then val else 0 end) as Salary, sum(case when salary_component_type='bonus' then val else 0 end) as bonus, sum(case when salary_component_type='hike_percent' then val else 0 end) as hike_percent from emp_compensation group by 1) select emp_id,Case when Salary then 'Salary' end as salary_component_type, case when salary then Salary end as val from pviot union select emp_id,Case when bonus then 'bonus' end as salary_component_type, case when bonus then bonus end as val from pviot union select emp_id,Case when hike_percent then 'hike_percent' end as salary_component_type, case when hike_percent then hike_percent end as val from pviot;