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

Is there any way we can use without union

8/141

awesome vide

Your solution is the more general one but an alternate solution can be proposed for this case ONLY since there is only 1 salary, 1 bonus, 1 hike per emp_id as:

with cte1 as
(select *, row_number() over() as rn_sal from
(select val as salary from interview_test_14 where salary_component_type='salary') sal_table)
, cte2 as
(select *, row_number() over() as rn_bon from
(select val as bonus from interview_test_14 where salary_component_type='bonus') bon_table)
, cte3 as
(select *, row_number() over() as rn_hike from
(select val as hike_percent from interview_test_14 where salary_component_type='hike_percent') sal_table)
select rn_sal as emp_id,salary,bonus,hike_percent
from cte1 join cte2 on rn_sal=rn_bon join cte3 on rn_bon=rn_hike;

However, a case and summing operation has to be used like the one you did if there exist multiple values for each emp_id which is pretty much the most general case of a pivotted table. Thanks for your approach!

Very nicely explained

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

Thank you valuable share

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?

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?

This is very helpful!
Thanks

Hello sir i have scenario where i have dynamic columns can you guide what can be solution for that.

Thank you for your efforts : )

Instead of "SUM" can we use "MIN" and "MAX"?

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

Thanks a lot sir for explaining in detail.

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

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

I am a big fan if you Ankit 💓

how to create duplicate table ( with new table name) in oracle with existing table values

create table old as
select emp_id,
sum(case when salary_component_type ='salary' then val end )as salary ,
sum(case when salary_component_type ='hike_percent' then val end) as hike_percent,
sum(case when salary_component_type ='bonus' then val end) as bonus
from emp_compensation
group by emp_id ;

How to create a copy of an Oracle table without copying existing data but table structure should remain same?

create table old as
select emp_id,
sum(case when salary_component_type ='salary' then val end )as salary ,
sum(case when salary_component_type ='hike_percent' then val end) as hike_percent,
sum(case when salary_component_type ='bonus' then val end) as bonus
from emp_compensation
where 1=0;
group by emp_id ;

I have one doubt, if I have string like first_name in column and how to eliminate those Null values just like you did using Sum function in case of numbers. anyone knows the solution?

Dynamic sp sikhaoooo kitne barre bol rha hu

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

Sir make please plsql series.

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

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

this is what I was searching for.. thanks man

Thank you so much ! 🙂

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;

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

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

True superhero👍

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

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!

sir there is one doubt for me correct me if I'm wrong as you have shown in the video there are only a few rows so we can use the case statement but if there is n number of rows how to do then we can't use the case function right.

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

Thank you, this help me a lot.

Man you are an amazing person helping a lot of people

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

Hi, how to unpivot data in Athena SQL

This was the first video of yours that I watched and it's enough to subscribe to your channel! 👍🏼

I think, We can also use MIN/MAX instead of SUM, isn't it ?

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

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.

So fantastic. Thank you so much. :)

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

Great session 👍

Great work Ankit so helpful thank you.

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 ?