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

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;

[ankitbansal]

Master the art of SQL with my zero to hero SQL for data analytics course.

[as-youtuber]

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

[ankush_in_sync]

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.

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

[sanchitvaid]

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!

[ratiranjannayak]

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

[bhooshan]

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

[CricketLivejavwad]

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

[lokeshvaishnav]

Man you are an amazing person helping a lot of people

[shoaibshaikh]

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

[girishggirishg]

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

[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

[brianducharme]

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

[rfgkgxx]

🎯 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

[sujaa]

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

[mnogqlx]

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.

[Ashu]

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

[Exceltech]

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

[SachinKumarHS]

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

[shubhamgoyal]

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