SQL Convert Rows to Columns and Columns to Rows without using Pivot Functions
Вставка
- Опубліковано 26 лис 2024
- 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;
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
Man you might don't know how much you are helping other people. True gem
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.
Cheers 🥳
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.
val is a column in the table 😊
@@ankitbansal6 pls suggest how to study from so many videos..is there any pdf of these available.?
@@jececdept.9548 just solve 1 question at a time everyday
Man you are an amazing person helping a lot of people
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!
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
Glad it helped you 😊
Practising from your videos before my interview. Thanks for the videos.
Best of luck
One of the best channels, thank you very much Ankit!
My pleasure
Sir you are brilliant, so much easier then using SQL pivot operator. Thank you kindly.
Happy to help
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.
Excellent
I got a query related to this, thank you now i got some clarity.
🎯 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
it's hard to see so so clear and easy understanding video. thank you
Glad it was helpful!
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 :)
Glad it helped!
Thank you sir @@ankitbansal6
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
Yes, you are right
This was an awesome tutorial! Very informative without the extra puff. Thank you so much!
Glad it was helpful!
Thanks Ankit for your time on this video 😊 appreciate all your effort
this is what I was searching for.. thanks man
Good one ankit, I am watching daily 1-3 video from your channel to prepare for faang
Awesome
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???
There is only one not null value in each group. So max min avg all same ..
@@ankitbansal6 ok
Really a great concept, explained very well.
keep doing what you are doing really it is very helpful.
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
I have several videos on my channel using that concept. 😊
Your videos has golden content! Just wowwwww!
🙏🙏
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
It's really awesome video. I have learnt some great insights from this video
Glad to hear that!
Great video! Excellent step-by-step explanation. Thank you.
🙏
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
Keep watching
Very much helpful video. Ankit sir please upload such more complex query video
Great work Ankit so helpful thank you.
Glad it helped
Well explained... Please make some videos on path to join Amazon as a Business Intelligence Engineer or Data Engineer.
Sure.
This was great and well explained. Thanks
Glad you enjoyed it!
@Ankit - Awesome very well explained !
Thanks a lot sir for explaining in detail.
You are most welcome
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.
Check out the complex SQL problems playlist
Thanks for explanation. It was nice
Thanks for sharing much value , a small request if you can make such more videos
god bless you
Sure 😊
@@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
Thanks very well explained...!
Glad it was helpful!
Informative content ❣
Very nicely explained
Keep watching😊
Great session 👍
Glad you enjoyed it
very useful 👌👌👍👍
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
Row to column is pivot and reverse in unpivot.
Thank you so much sir for your help. 👍👍🙏
Keep watching 💪
Really good explanation, great job :)
Thank you 😊
Thank you for your efforts : )
My pleasure!
This is very helpful!
Thanks
Glad it was helpful!
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.
There is pivot function to do that. I will make a separate video.
Thank you, this help me a lot.
Glad it helped!
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
You can use string agg function ..or can use min , max
Thankyou so much for this video
super sir make a more interview quations.....
Today i learn new thing in sql
And I also share on LinkedIn...
Great explanation
Great video
Glad you enjoyed it
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
Alternatively you can use pivot and unpivot functions.
Hi Ankit, Thanks for creating this video . It was really helpful . Can you please create more examples around this.
Sure
Nice video! Thank you.
Well explained video!
True superhero👍
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 valuable share
Thank you Ankit for awesome explanation.
Is there another video to practice this topic?
There are many in this playlist
Complex SQL Questions for Interview Preparation: ua-cam.com/play/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb.html
Are case statements faster than pivot ?
I would say you have the flexibility to give multiple case conditions based on the requirements.
Thanks for replying so promptly. Do you have or planning to release any spark project based content?
So fantastic. Thank you so much. :)
You are so welcome!
Thank you!
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
You can use a similar case when statements as explained in video. Just use max or min along with case when.
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.
Thanks friend very helpful
Welcome
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
Use create table as instead of into
@@ankitbansal6
sir used create table statement also but it is not working
is their any other alternative?
I am a big fan if you Ankit 💓
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!!
Hello,
Do we do this with dates in place of numbers?
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 ?
I haven't done that yet. Planning that.
will query work if we have millions of records ? we can not union every Time
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?
You just need to create one case statement with multiple when ..that will do
Hi Hiya, Please post your solution . It will be helpful
I guess this is a hackerrank question, you can just group by and take min/max.
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 !
@@ankitbansal6 It will not solve problem if we use so
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.
Yes it works on string.
@@ankitbansal6 thank you for the clarification. 😊
excellent
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
Okay
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 !!
You mean you want to load data from files in your system to SQL server right ?
@@ankitbansal6 YES
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.
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.
Please make a video on using Pivot functions and merge statement
Sure.
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
Thank you so much ! 🙂
You're welcome 😊
Hi Ankit - Thanks for Video. How we can use this if we have char/varchar instead of integers in the table
Use Max min
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?
@ankit How to dynamically pick pivot rows to columns without using pivot function?
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?
It's optimised technique only.
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!
Hi Ankit, what do we use in case of phone numbers with hyphen instead of sum. Thanks
Max or min
This is awesome!! Would this syntax work in mysql too?
Yes it will
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;
The same query how can we perform if the value has non numeric values.
Thank you so much
Glad you liked it.
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.
Man you might don't know how much you are helping other people. True gem
🙏
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
You have only 2 options. Either use pivot unpivot functions or do like in video.
Is 'select into' syntax for creating new table worked in MySQL ? I got error