Learn Window Functions, CTEs, Recursive Queries, and Pivot/Unpivot - Textnotes

Learn Window Functions, CTEs, Recursive Queries, and Pivot/Unpivot

Master advanced SQL queries with this comprehensive guide. Learn how to use window functions, Common Table Expressions (CTEs), recursive queries, and pivot/unpivot operations. Practice ranking employees, handling hierarchical data, and summarizing sales by month.

1. Introduction

Advanced SQL queries allow you to perform complex data analysis, reporting, and hierarchical operations.

  1. Use window functions for ranking and analytics.
  2. Use CTEs and recursive queries for readable and reusable query structures.
  3. Pivot/unpivot operations help reshape data for reporting and dashboards.

2. Window Functions

Window functions perform calculations across a set of rows related to the current row without collapsing the result set.

Common Functions:

  1. ROW_NUMBER() – Sequential numbering of rows.
  2. RANK() – Ranking with gaps for ties.
  3. DENSE_RANK() – Ranking without gaps for ties.
  4. NTILE(n) – Divides rows into n equal parts.

Example – Rank employees by salary in each department:


SELECT
Name,
Department,
Salary,
ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RowNum,
RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS Rank,
DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS DenseRank
FROM Employees;

3. Common Table Expressions (CTEs)

CTEs allow you to create temporary result sets that can be referenced within a query.

Syntax:


WITH CTE_Name AS (
SELECT ...
)
SELECT * FROM CTE_Name;

Example – Hierarchical data (employees reporting structure):


WITH EmployeeHierarchy AS (
SELECT ID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ID, e.Name, e.ManagerID, eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.ID
)
SELECT * FROM EmployeeHierarchy;

4. Recursive Queries

  1. Recursive CTEs help traverse hierarchical data (e.g., org charts, categories).
  2. Base query defines the root level, recursive query references the CTE itself.

Example – Employee reporting chain:


WITH EmployeeCTE AS (
SELECT ID, Name, ManagerID
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ID, e.Name, e.ManagerID
FROM Employees e
INNER JOIN EmployeeCTE cte ON e.ManagerID = cte.ID
)
SELECT * FROM EmployeeCTE;

5. Pivot / Unpivot Data

5.1 Pivot

  1. Converts rows into columns for summary reports.

Example – Pivot sales data per month:


SELECT *
FROM (
SELECT EmployeeID, Month, SalesAmount
FROM Sales
) AS SourceTable
PIVOT (
SUM(SalesAmount)
FOR Month IN ([Jan], [Feb], [Mar], [Apr])
) AS PivotTable;

5.2 Unpivot

  1. Converts columns into rows.

Example:


SELECT EmployeeID, Month, SalesAmount
FROM PivotTable
UNPIVOT (
SalesAmount FOR Month IN ([Jan], [Feb], [Mar], [Apr])
) AS UnpivotTable;

6. Practical Exercises

  1. Rank employees by salary in each department using ROW_NUMBER(), RANK(), DENSE_RANK().
  2. Create a CTE to list all employees in hierarchical order.
  3. Use a recursive CTE to display the full reporting chain of managers.
  4. Pivot monthly sales data for all employees to view columns as months.
  5. Unpivot pivoted sales data to restore the original row-wise format.

7. Tips for Beginners

  1. Window functions do not reduce rows; they just add analytic columns.
  2. Use CTEs for readable, maintainable queries instead of subqueries.
  3. Recursive CTEs are powerful for hierarchical data but test for infinite loops.
  4. Pivot/unpivot are ideal for reporting and dashboards.
  5. Combine these techniques with indexes and optimization for performance.


Next Step: After mastering advanced SQL queries, the next module is Data Types and Constraints, where you’ll learn to enforce data integrity using primary keys, foreign keys, unique, not null, and check constraints.