SQL: Breaking down Advance sql components — Part 2

Comprehensive coverage of SQL components

Introduction

This article is an extension to my previous article on SQL advance components — Link.

Lets dive in —

1. Pivoting

Pivoting in SQL refers to the process of transforming data from rows into columns, enabling a better analysis of aggregated data.
It is often used to create summary tables, converting distinct values in a column into separate columns, and displaying aggregated metrics for those values.

Table — sales

-- Query
SELECT *
FROM (
    SELECT Year, region, revenue
    FROM sales
) AS source
PIVOT (
    SUM(revenue)
    FOR region IN ([north], [south])
) AS pivot_table;


-- SIMILAR QUERY in MYSQL
SELECT 
    Year,
    SUM(CASE WHEN region = 'north' THEN revenue ELSE 0 END) AS north,
    SUM(CASE WHEN region = 'south' THEN revenue ELSE 0 END) AS south
FROM user_database.sales
GROUP BY Year;

2. User-Defined Functions