CodeBlanks Logo Icon CodeBlanks
Login Register

Dynamic pivot

Pivot when the column count changes.

Dynamic pivot

Dynamic pivot makes categories into columns automatically. Not done directly with plain SQL.

-- adım 1: kategorileri bul
-- adım 2: SQL'i dinamik üret
-- adım 3: üretilen SQL'i çalıştır

This query requires a stored procedure.

Dynamic pivot — details

Dynamic pivot uses the database's dynamic SQL feature. Needed when the category count is unknown upfront.

In that case the category list is queried first, the query text is built from it and run with EXECUTE; if user input is involved you must watch out for SQL injection.

Practice Dynamic pivot

In a stored procedure, find the category list and generate dynamic pivot SQL.

Starter code

query.sql
-- dynamic pivot
SELECT DISTINCT

Sample solution

query.sql
SELECT DISTINCT category FROM sales;

Expected output

category
Kitap
Elektronik

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 Dynamic pivot: match the terms

Term Meaning
dynamic automatic
category column
stored procedure procedure
flexible variable

2 Dynamic pivot

Question 
if the categories are not known ____ (dynamic pivot)
Answer 
if the categories are not known in advance (dynamic pivot)

Explanation: A dynamic pivot is needed when the categories are not known in advance.

3 What does dynamic pivot require?

  • TRIGGER
  • INDEX
  • VIEW
  • Dynamic SQL Correct answer

Explanation: Dynamic pivot requires dynamic SQL.

All lessons in Advanced

1 CTE

  1. What is a CTE?
  2. Using WITH
  3. Multiple CTEs
  4. CTE vs subquery
  5. CTE with JOIN
  6. Summary - CTE

2 Recursive CTE

  1. What is a recursive CTE?
  2. Anchor and recursive parts
  3. Walk a hierarchy
  4. Tracking depth
  5. Stopping recursion
  6. Summary - Recursive CTE

3 Window functions

  1. What is a window function?
  2. ROW_NUMBER
  3. RANK and DENSE_RANK
  4. LAG and LEAD
  5. PARTITION BY
  6. Summary - Window functions

4 Project: Analytics report

  1. Analytics design
  2. CTE summary
  3. Top users with ROW_NUMBER
  4. Growth with LAG
  5. Final report

5 Pivot and unpivot

  1. What is pivot?
  2. Pivot with CASE
  3. FILTER clause
  4. Unpivot
  5. Dynamic pivot
  6. Summary - Pivot

6 Materialized view

  1. What is a materialized view?
  2. CREATE
  3. REFRESH
  4. View vs materialized view
  5. Index on materialized views
  6. Summary - Materialized view

7 Stored procedure

  1. What is a procedure?
  2. CREATE PROCEDURE
  3. Parameters
  4. CALL
  5. Control flow
  6. Summary - Stored procedure

8 Function

  1. What is a function?
  2. CREATE FUNCTION
  3. RETURN
  4. Calling a function
  5. Function vs procedure
  6. Summary - Function

9 Trigger

  1. What is a trigger?
  2. BEFORE and AFTER
  3. INSERT trigger
  4. UPDATE trigger
  5. Write an audit trigger
  6. Summary - Trigger

10 Transaction isolation

  1. What is isolation?
  2. READ UNCOMMITTED
  3. READ COMMITTED
  4. REPEATABLE READ
  5. SERIALIZABLE
  6. Summary - Isolation

11 Locking

  1. What is a lock?
  2. Shared and exclusive locks
  3. Deadlock
  4. SELECT FOR UPDATE
  5. Lock timeout
  6. Summary - Locking

12 Query optimization

  1. What is EXPLAIN?
  2. Seq scan vs index scan
  3. Cost
  4. Sargable
  5. N+1 problem
  6. Summary - Optimization

13 Partitioning

  1. What is partitioning?
  2. Range partition
  3. List partition
  4. Hash partition
  5. Partition pruning
  6. Summary - Partitioning

14 Advanced final

  1. Hierarchy report
  2. Window analysis
  3. Procedure and trigger
  4. Optimization