Capgemini SQL Interview Question 2024 | Transform Rows Into Columns

[@rohitconnect]

instead of using pivot/ cross tab we can also do the below which is can run on any platform
SELECT months,
sum(case when category='Clothing' then Amount end) as Clothing,
sum(case when category='Electronic' then Amount end) as Electronic
from Months
group by months;

[@datasciencecorner]

Yes, we can use case statements as well!

[@yatharthchauhan7407]

My solution with simple case statement:
select
month,
sum(case when category = 'Clothing' then amount else 0 end) as Clothing,
sum(case when category = 'Electronics' then amount else 0 end) as Electronics
from
sales_data_ec
group by month

[@datasciencecorner]

Great! Keep practicing :)

[@udaykumar-k1j5o]

select month,clothing,electronics
from (
select month,category,amount
from sales_data
)as temp
pivot
(
max(amount)
for category in (clothing,electronics)
)piv
order by month

[@saipraneeth1660]

Select month, [electronics], [clothing] from ( select month, category,amount from input_tbl) as source
PIVOT(sum(amount) for category in [(electronics),(clothing)] as pivot_table

[@datasciencecorner]

Nice. Keep practicing :)

[@dasubabuch1596]

select month,
sum(case when category = 'Clothing' then Amount end) as Clothing,
sum(case when category = 'Electronics' then Amount end) as Electronics
from sales_data
group by month
order by month;

[@datasciencecorner]

Nice. Keep rocking :)

[@TarabShaikh-q9n]

select
month,
SUM(case when category = 'Clothing' then amount end) as "Clothing",
SUM(case when category = 'Electronics' then amount end) as "Electronics"
from sales
group by month
order by month;

[@datasciencecorner]

Great! Keep practicing :)

[@MusicalShorts-hn1px]

Would be great if you could provide table creation vommand in description

[@datasciencecorner]

I have added the queries to create the table and insert the values. Happy Learning :)

[@chandanpatra1053]

why you are not providing create & insert statement after every video in the description box. I have given this a feedback.

[@datasciencecorner]

I have added the queries to create the table and insert the values. Will make a note of it to add in every video. Happy Learning :)

[@satishgs5355]

WITH cte_table AS(
SELECT month,
category,
amount,
CASE
WHEN month = 'January' THEN 1
WHEN month = 'February' THEN 2
WHEN month = 'March' THEN 3
WHEN month = 'April' THEN 4
END AS month_order
FROM sales_data
)
SELECT month,Clothing,Electronics
FROM (
SELECT *
FROM cte_table) AS source_table
PIVOT(
MAX(AMOUNT)
FOR CATEGORY IN(Clothing,Electronics)
) AS pivot_table
ORDER BY month_order

[@datasciencecorner]

Nice! Keep practicing :)

[@satishgs5355]

SELECT month,
SUM(CASE WHEN category='Clothing' then amount end) as Clothing,
SUM(CASE WHEN category='Electronics' then amount end) as Electronics

FROM sales_data
GROUP BY month;

[@datasciencecorner]

Great! Keep practicing well! Our best wishes to you :)

[@madhusudhanreddyt2838]

in sql server,
-- using the pivot operator
select month, [Clothing], [Electronics]
from
(
select month, category, amount from sales_data
) as Source_Data
PIVOT
(
SUM(amount) FOR category IN ([Clothing], [Electronics])
) as Pivot_Data;
-- without using the pivot operator
select
month,
SUM(case when category = 'Clothing' THEN amount else null end) as [Clothing],
SUM(case when category = 'Electronics' THEN amount else null end) as [Electronis]
from sales_data
group by
month;

[@datasciencecorner]

You're rocking :)

[@theinsightminer08]

SELECT
month,
MAX(CASE WHEN category = 'Clothing' THEN amount END) AS Clothing,
MAX(CASE WHEN category = 'Electronics' THEN amount END) AS Electronics
FROM sales_data
GROUP BY month;

[@datasciencecorner]

Great :)