Solving SQL Query | Rows to Column in SQL

In this video, we will solve an SQL Query where we will need to transform data from rows to column.
We can transform data from rows to column in SQL using PIVOT operator but not all RDBMS supports PIVOT. Microsoft SQL Server and Oracle supports PIVOT operator, whereas in PostgreSQL, we can transform rows to column using CROSSTAB function. Similarly in MySQL database, rows to column transformation can be done using the CASE statement.

Hence in this video, we shall first look at the SQL query problem statement. We understand the input data and also understand the expected output. After this we shall write SQL query to solve this problem using PIVOT in Microsoft SQL Server and in Oracle.
We shall then solve the same problem in PostgreSQL database using CROSSTAB function. Finally we shall also solve this query in MySQL database using the CASE statement.

All the datasets, scripts and SQL queries used in this video can be downloaded from my blog and from my discord channel. Links below:

If you would like to share any interesting SQL query with me so that I can make a video about it then do share them in the email below:

Timestamp:
00:00 Intro
01:58 Understanding the SQL Query Problem statement
04:20 Solving SQL Query in Microsoft SQL Server using PIVOT
26:59 Solving SQL Query in Oracle using PIVOT
32:46 Solving SQL Query in PostgreSQL using CROSSTAB
55:31 Solving SQL Query in MySQL using CASE statement

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

THANK YOU,
Thoufiq

[TheVaibhavdang]

Your content is what I am following from my initial days of learning sql. If possible please make more practise videos on sql with concepts every week/weekend so that our logic buildng can increase week by week. Thanks for the good content.

[Nizam_Ul_Mulk]

Bro you are one of the best trainers I could ever come across. To the point and the crystal clear. Thanks one again.

[SantanuRoy-oqnv]

Taufiq sir, absolutely clear the "without ORDER BY clause in postgre SQL Crosstab"...what ever customer is visible as per the sequence, CROSSTAB is grouping those visible data. That's why ORDER BY is mandatory otherwise this weird type data will be show. Thank you sir

[nataliebear]

This is the best crosstab explanation I've seen. Thank you!!!

[MOTIVAO]

Your videos are quite incredible, thank you Thoufiq for your patience and knowledge.

[mahamuthashameem]

till i watched this video, i was scared about pivot query, now i can understand it easily. thank yo so much for the video

[pw]

Good video, but for SQL Server i would recommend you look at the GROUPING SETS command.
With that you can rewrite your query to below :
When using "set statistics time on;" you can see it beeing a lot faster.

;with inner_data as
(
select
customer_id as Customer
, format(sales_date, 'MMM-yy') as sales_date
, cast(replace(amount, '$', '') as int) as amount
from sales_data
)
select *
from (
select
isnull(Customer, 'Total') as Customer,
isnull(sales_date, 'Total') as sales_date,
case when sign(sum(amount)) = -1 then concat('(', ABS(sum(amount)), ')$') else concat(sum(amount), '$') end as amount
from (
SELECT a.Customer, b.sales_date, 0 as amount
from
a inner join
b
a.Customer <> b.Customer and a.sales_date <> b.sales_date
union all
select



from inner_data ) a
group by GROUPING SETS( (Customer, sales_date), (Customer), (sales_date))
) as sales
pivot
(
max(amount)
for sales_date in ([Jan-21], [Feb-21], [Mar-21], [Apr-21]
, [May-21], [Jun-21], [Jul-21], [Aug-21]
, [Sep-21], [Oct-21], [Nov-21], [Dec-21], [Total])
) as pivot_table

[bala]

Thank you so much... helping multi tools at same time...

[kushalbaral]

Great content, helped with exactly what I needed for my current task at work. Thanks a lot !

[samadixyachine]

Although i am not an English speaker, that does not prevent me from following you. It is very well explained with well chosen examples.
I learned a lot thanks to you. Believe me you are the best. I copied the exact same query using crosstab and when i added the second
argument i got the error : column ("Jan-21") does not exist. I hope you can explain the origin of the problem to me. GREAT THANKS
(i replaced ("Jan-21") with (' 'Jan-21' ') and it worked)

[bobhutchins]

What an outstanding video! Ver well done! Thank you for putting it together. I have a question, though. I'm primarily a MySQL users but also use SQL Server. How could we set this up to be more dynamic? For example, let's say your column table covered 24 months and had 5 customers. Instead of writing out 24 lines of code each time (1 for each column) and then 24 lines multiple times, is there a way to write much fewer lines using variables, loops and a WITH clause? Thanks again - your videos have taught me so, so much!

[olganikitochkina]

This is the best explanation! Thank you so much ❤

[sohailahmed]

Garrett Evans Wow thank you Garrett, you've made my day!

[DHARMYOGCOM]

this is a great one. thanks. i always wanted to do pivot queries. never really got around to it. now i got to learn it with my current project. very helpful video. thanks

[LuLu-inix]

your video is very helpful for me. thank you. 👍

[abhijeetwadkar]

Just wanted to say Thanks!!! Your sql joins video helped me crack my first tech interview. Kep up the good work

[karunakaranr]

Very helpful and thank you for your time and effort to make this excellent video.

[Anonymous_it_is]

Everytime you upload a new video, it brings abundance of knowledge in our lives. Thanks for this quality content.
Maza aa jata hai aapka explanation sunke😁

Please consider making videos on topics such as Indexing(in depth) and database designing if possible.

[santoshkumarchannel]

I also searching the same....But finally got from you
Thank you

[pitou]

that was some useful stuff man, thankyou so much.
mark my words "im going to get a data analyst job soon " and you are playing very important role in my journey.