Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 38 ยท SQL Dialect and Types

PIVOT, Sampling, Sequences and Macros

Reshape and reuse SQL in DuckDB: PIVOT and UNPIVOT, USING SAMPLE, CREATE SEQUENCE and generated columns, scalar and table macros, plus portable stand-ins using conditional aggregation.

This lesson rounds off DuckDB's SQL dialect with four tools you will use again and again: PIVOT and UNPIVOT to turn rows into columns and back, sampling to look at a slice of a huge table, sequences and generated columns to produce IDs and derived values, and macros to name a piece of SQL so you can reuse it.

PIVOT, UNPIVOT, USING SAMPLE and CREATE MACRO are DuckDB syntax; they appear as read-only blocks. Every one has a portable equivalent, and the runnable stand-ins show it, so you also learn what the shortcuts expand to.

PIVOT: rows into columns

Suppose you have one row per country per year and you want one row per country with a column for each year, a "wide" table suitable for a spreadsheet or chart. In DuckDB:

SQL
-- DuckDB (read-only example)
PIVOT sales
ON year
USING sum(amount)
GROUP BY country;

Read it as: take sales, spread the values of year into columns, fill each with sum(amount), one row per country. You can restrict the new columns with ON year IN (2024, 2025), use several aggregates in USING, and add multiple ON columns. A PIVOT also works inside a larger query:

SQL
-- DuckDB (read-only example)
SELECT * FROM (PIVOT sales ON year USING sum(amount) GROUP BY country) WHERE "2025" > 100;

The portable method is conditional aggregation: one SUM(CASE ...) per new column. It works in every database, and you must list the columns yourself:

SQL Stand-in: pivot with conditional aggregation ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DuckDB's PIVOT finds the distinct values on its own, so you do not need to know them in advance. The standard SQL PIVOT of other databases requires a fixed list.

UNPIVOT: columns into rows

The reverse is "wide to long", needed to chart wide data or clean spreadsheet exports:

SQL
-- DuckDB (read-only example)
UNPIVOT monthly_sales
ON jan, feb, mar
INTO NAME month VALUE amount;

-- or select the columns by pattern
UNPIVOT monthly_sales ON COLUMNS(* EXCLUDE (region)) INTO NAME month VALUE amount;

The portable version stacks one SELECT per column with UNION ALL:

SQL Stand-in: unpivot with UNION ALL

Press Run (or Ctrl + Enter) to see the result here.

Sampling

On a table with 500 million rows, you do not need all of them to explore it. DuckDB can sample:

SQL
-- DuckDB (read-only example)
SELECT * FROM events USING SAMPLE 10%;                 -- about a tenth of the rows
SELECT * FROM events USING SAMPLE 1000 ROWS;           -- exactly 1,000 rows
SELECT * FROM events USING SAMPLE reservoir(1000 ROWS) REPEATABLE (42);
SELECT * FROM events USING SAMPLE 5% (bernoulli);
SELECT * FROM events TABLESAMPLE system(1%);
SELECT * FROM events LIMIT 10%;
  • Bernoulli sampling decides row by row; system sampling chooses whole chunks (faster, less random); reservoir gives an exact row count.
  • REPEATABLE (seed) makes the sample the same each time, so your analysis can be reproduced.
  • Sampling is different from LIMIT: LIMIT takes the first rows, which may not represent the whole table.

SQLite has no sampling syntax, but ordering by a hash gives a repeatable stand-in. This takes roughly a quarter of the products:

SQL Stand-in: a repeatable sample ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Sequences, auto-increment and generated columns

DuckDB has no AUTOINCREMENT; you use a sequence:

SQL
-- DuckDB (read-only example)
CREATE SEQUENCE ticket_id START 1;

CREATE TABLE tickets (
  id    INTEGER DEFAULT nextval('ticket_id') PRIMARY KEY,
  title VARCHAR,
  opened TIMESTAMP DEFAULT current_timestamp
);

INSERT INTO tickets (title) VALUES ('Printer on fire'), ('No coffee');
SELECT currval('ticket_id');          -- 2

You can also use uuid() or gen_random_uuid() for random identifiers. Generated columns compute a value from other columns and store or derive it automatically:

SQL
-- DuckDB (read-only example)
CREATE TABLE rectangles (
  w DOUBLE, h DOUBLE,
  area DOUBLE GENERATED ALWAYS AS (w * h) VIRTUAL
);

SQLite's INTEGER PRIMARY KEY auto-numbers, and it also has generated columns. A runnable version:

SQL Stand-in: auto-numbering and generated columns

Press Run (or Ctrl + Enter) to see the result here.

Macros: reusable SQL

A macro is a named expression or query that DuckDB expands when it plans your statement. It is lighter than a user-defined function and needs no programming language.

Scalar macros return a value:

SQL
-- DuckDB (read-only example)
CREATE MACRO with_tax(price, rate := 0.2) AS round(price * (1 + rate), 2);

SELECT with_tax(100);                  -- 120.0
SELECT with_tax(100, rate := 0.07);    -- 107.0
SELECT name, with_tax(price) FROM products;

Table macros return a whole result set, like a parameterized view:

SQL
-- DuckDB (read-only example)
CREATE MACRO top_products(n) AS TABLE
  SELECT name, price FROM products ORDER BY price DESC LIMIT n;

SELECT * FROM top_products(3);

Macros support named and default parameters, can call other macros, and can be created as CREATE OR REPLACE MACRO or CREATE TEMP MACRO. In SQLite (no macros) the closest thing is a view for table results:

SQL Stand-in: a view as a reusable query ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Macros really shine when you hide the awkward bits of repeated cleanup, such as a clean_phone(x) macro that strips non-digits with regexp_replace, and put them in an init script that every project reads.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does DuckDB's PIVOT do?

    PIVOT spreads values from rows into columns, filling them with an aggregate.
  2. 2. Which portable technique replaces PIVOT in databases without it?

    One SUM(CASE ...) expression per output column gives a pivoted result in any SQL database.
  3. 3. What does USING SAMPLE 10% ... REPEATABLE (42) give you?

    Sampling picks rows at random, and a seed makes the choice repeatable.
  4. 4. How do you create an auto-incrementing ID in DuckDB?

    DuckDB uses sequences, and nextval() supplies the default value of the column.
  5. 5. What is a table macro?

    A table macro is declared AS TABLE and called in the FROM clause with arguments.

Summary

  • PIVOT and UNPIVOT reshape between long and wide; conditional aggregation and UNION ALL are the portable forms.
  • USING SAMPLE (Bernoulli, system, reservoir) explores big tables; REPEATABLE makes it reproducible.
  • Sequences with nextval() give auto-increment; generated columns derive values.
  • Scalar and table macros name reusable SQL, with named and default parameters.

Next: Reading CSV files