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.

Exercise Dynamic pivot

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

Show example solution

SELECT DISTINCT category FROM sales;

Test yourself

Dynamic pivot: match the terms

dynamic

automatic
category

column
stored procedure

procedure
flexible

variable

Dynamic pivot

if the categories are not known ____ (dynamic pivot)

Show answer

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

What does dynamic pivot require?
- TRIGGER
- INDEX
- VIEW
- Dynamic SQL
Show answer

Dynamic SQL — Dynamic pivot requires dynamic SQL.

Next lesson: Summary - Pivot

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies