SQL · Intermediate

SQL pivot: rows to columns and unpivot

18 min read

Why it matters

A SQL pivot turns rows into columns: one row per country with a column per month, one row per student with a column per subject. Managers read wide tables; databases and BI tools work best with long ones. Analysts move between the two shapes all the time, and "pivot this table in SQL" is a classic interview task because it tests CASE, GROUP BY and NULL handling at once. SQL appears in 88% of Data Analyst job descriptions we studied, and Excel-style pivot outputs are one of the most requested report formats.

This lesson covers the portable way to pivot (conditional aggregation), the PIVOT keyword where it exists, dynamic pivots, PostgreSQL's crosstab, unpivoting back to long form, and how to prove nothing was lost on the way.

The concept

Long vs wide

Long (tidy)	Wide (pivoted)
One row per country per month: `country, month, revenue`	One row per country: `country, 2011-01, 2011-02, ...`
New month = new rows; schema never changes	New month = new column; schema changes
Easy to filter, join, aggregate, feed to Power BI or Tableau	Easy for people to read and compare across
Missing months are simply absent	Missing months become NULL cells

Rule of thumb: store long, present wide. Pivot at the last step, for the output someone reads.

Conditional aggregation: the pivot that works everywhere

Group by the row label, and for each column write an aggregate that only looks at the matching rows:

sql

SELECT country,
       SUM(CASE WHEN month = '2011-01-01' THEN revenue END) AS jan,
       SUM(CASE WHEN month = '2011-02-01' THEN revenue END) AS feb
FROM monthly_country
GROUP BY country;

The CASE returns the value for matching rows and NULL for the rest, and SUM ignores NULLs. PostgreSQL, SQLite and DuckDB also accept the shorter SUM(revenue) FILTER (WHERE month = '2011-01-01'). Use MAX or MIN instead of SUM when each cell should hold exactly one value (marks, statuses).

Leaving out ELSE matters. CASE WHEN ... THEN revenue ELSE 0 END turns "no data" into 0, which is a decision, not a default (see NULL vs 0 below).

The PIVOT keyword

Some databases have a dedicated syntax. PostgreSQL and MySQL do not.

Tab 1

sql

SELECT country, [2011-07], [2011-08], [2011-09]
FROM (SELECT country, ym, revenue FROM monthly_country_ym) AS src
PIVOT (SUM(revenue) FOR ym IN ([2011-07], [2011-08], [2011-09])) AS p;

Tab 2

sql

SELECT *
FROM (SELECT country, ym, revenue FROM monthly_country_ym)
PIVOT (SUM(revenue) FOR ym IN ('2011-07', '2011-08', '2011-09'))
ORDER BY country;
-- newer Snowflake versions also accept IN (ANY ORDER BY ym)

Tab 3

sql

SELECT *
FROM (SELECT country, ym, revenue FROM monthly_country_ym)
PIVOT (SUM(revenue) FOR ym IN ('2011-07' AS m2011_07, '2011-08' AS m2011_08))

Tab 4

sql

PIVOT (SELECT country, ym, revenue FROM monthly_country_ym)
ON ym
USING SUM(revenue);   -- finds the columns itself

The trap that catches everyone in SQL Server and Snowflake: PIVOT groups by every column of the source that is not pivoted or aggregated. Feed it a table with an extra invoice_no column and you get one row per invoice instead of one per country. Always pivot from a subquery that selects exactly the columns you need.

Static vs dynamic pivots

Plain SQL needs the column list written out when you write the query. If the columns depend on the data (new months, new products), you have three options:

Generate the SQL: query the distinct values, build the column list as a string, and run it (psql's \gexec, dynamic SQL in a stored procedure, or Python).
Use a database that discovers columns: DuckDB's PIVOT ... ON col and Snowflake's IN (ANY).
Do not pivot in SQL: return long data and let Excel, Power BI, Tableau or pandas pivot it. This is usually the right answer for dashboards.

NULL vs 0 in empty cells

A cell with no data is NULL. Whether it should show 0 depends on the measure:

Additive totals (revenue, orders, units): no sales really is 0, so COALESCE(SUM(...), 0) is fine for display.
Averages, rates, scores, latest status: there is no value, and 0 would be a lie. A missing exam is not a score of 0.
Anything you will average across columns later: keep NULL, or the zeros pull the average down (the worked example shows by how much).

Unpivot: wide back to long

PostgreSQL: CROSS JOIN LATERAL (VALUES ('2011-01', w.jan), ('2011-02', w.feb)) AS v(month, revenue). One scan of the table, any number of columns.
Anywhere: SELECT country, 'jan' AS month, jan AS revenue FROM wide UNION ALL SELECT country, 'feb', feb FROM wide .... Works everywhere, but scans the table once per column.
UNPIVOT keyword in SQL Server, Snowflake, BigQuery and DuckDB. Note that they drop NULL cells by default, so the row count changes.

Column names

Pivoted column names come from data, so they hit naming rules. Names like 2011-07 must be quoted ("2011-07" in PostgreSQL, [2011-07] in SQL Server); BigQuery needs an alias. PostgreSQL silently truncates names longer than 63 bytes and allows at most 1,600 columns per table; SQL Server allows 1,024. Columns generated from values also come out in sort order of the text, so Apr, Aug, Dec instead of Jan, Feb, Mar: use sortable labels like 2011-01 or order the list yourself.

Worked example: monthly revenue by country on Online Retail

We use the UCI Online Retail dataset (541,909 invoice lines from a UK online gift shop, CC BY 4.0) in PostgreSQL 16, with a DuckDB comparison. Every output below is from a real run.

Load the data

Download online-retail-dataset.csv (link in Further reading), start psql in the same folder, and run:

sql

CREATE TABLE raw_retail (
  invoice_no   text,
  stock_code   text,
  description  text,
  quantity     int,
  invoice_date text,
  unit_price   numeric(10,3),
  customer_id  int,
  country      text
);
\copy raw_retail FROM 'online-retail-dataset.csv' WITH (FORMAT csv, HEADER true)

Output

CREATE TABLE
COPY 541909

Step 1: build the long table

sql

CREATE TABLE monthly_country AS
SELECT country,
       date_trunc('month', to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI'))::date AS month,
       round(sum(quantity * unit_price), 2) AS revenue
FROM raw_retail
WHERE quantity > 0 AND unit_price > 0
GROUP BY 1, 2;

SELECT count(*) AS rows,
       count(DISTINCT country) AS countries,
       count(DISTINCT month) AS months,
       sum(revenue) AS revenue
FROM monthly_country;

Output

SELECT 302
 rows | countries | months |   revenue   
------+-----------+--------+-------------
  302 |        38 |     13 | 10666684.54
(1 row)

38 countries times 13 months would be 494 combinations, but there are only 302 rows. The other 192 country-months had no sales and simply do not exist in long form. Keep that number in mind for step 7.

Step 2: pivot with conditional aggregation

One row per country, one column per month from December 2010 to November 2011 (December 2011 stops on the 9th, so it is left out). The UK is excluded because its numbers (£430,000 to £1.3 million a month) would dwarf the rest.

sql

SELECT country,
       round(sum(CASE WHEN month = DATE '2010-12-01' THEN revenue END)) AS "2010-12",
       round(sum(CASE WHEN month = DATE '2011-01-01' THEN revenue END)) AS "2011-01",
       round(sum(CASE WHEN month = DATE '2011-02-01' THEN revenue END)) AS "2011-02",
       round(sum(CASE WHEN month = DATE '2011-03-01' THEN revenue END)) AS "2011-03",
       round(sum(CASE WHEN month = DATE '2011-04-01' THEN revenue END)) AS "2011-04",
       round(sum(CASE WHEN month = DATE '2011-05-01' THEN revenue END)) AS "2011-05",
       round(sum(CASE WHEN month = DATE '2011-06-01' THEN revenue END)) AS "2011-06",
       round(sum(CASE WHEN month = DATE '2011-07-01' THEN revenue END)) AS "2011-07",
       round(sum(CASE WHEN month = DATE '2011-08-01' THEN revenue END)) AS "2011-08",
       round(sum(CASE WHEN month = DATE '2011-09-01' THEN revenue END)) AS "2011-09",
       round(sum(CASE WHEN month = DATE '2011-10-01' THEN revenue END)) AS "2011-10",
       round(sum(CASE WHEN month = DATE '2011-11-01' THEN revenue END)) AS "2011-11"
FROM monthly_country
WHERE country IN ('Netherlands', 'EIRE', 'Germany', 'France', 'Australia', 'Japan')
GROUP BY country
ORDER BY sum(revenue) DESC;

Output

   country   | 2010-12 | 2011-01 | 2011-02 | 2011-03 | 2011-04 | 2011-05 | 2011-06 | 2011-07 | 2011-08 | 2011-09 | 2011-10 | 2011-11 
-------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
 Netherlands |    8784 |   26611 |   23012 |   22416 |    2977 |   29186 |   26858 |      26 |   40328 |   26937 |   40709 |   25874
 EIRE        |   10033 |   21904 |   12249 |   22198 |    7571 |   18009 |   24864 |   42790 |   18354 |   43087 |   24318 |   30738
 Germany     |   15241 |   16911 |    9581 |   14393 |   12316 |   25751 |   13274 |   16441 |   19221 |   18091 |   31638 |   28025
 France      |    9616 |   17740 |    8516 |   14590 |    5530 |   17615 |   16348 |   10000 |   13811 |   23428 |   33485 |   31759
 Australia   |    1033 |    9018 |   14695 |   17224 |     772 |   13638 |   25188 |    4964 |   22489 |    5107 |   17151 |    7243
 Japan       |    7705 |         |    5781 |      49 |    6549 |    1351 |    1607 |    3854 |         |     112 |    1948 |    8460
(6 rows)

Now the table is easy to read: the Netherlands earned only £26 in July 2011, against £26,858 in June and £40,328 in August, and Japan has two empty months. Notice ORDER BY sum(revenue): you can sort a pivot by any aggregate over the group, not just by pivoted columns.

Step 3: NULL or 0 for Japan's empty months?

For a revenue table, showing 0 in January and August is fair: Japan bought nothing. But watch what happens to an average:

sql

SELECT country,
       round(avg(revenue)) AS avg_of_months_with_sales,
       round(sum(revenue) / 12) AS avg_over_12_months,
       count(*) AS months_with_sales
FROM monthly_country
WHERE country = 'Japan' AND month BETWEEN DATE '2010-12-01' AND DATE '2011-11-01'
GROUP BY country;

Output

 country | avg_of_months_with_sales | avg_over_12_months | months_with_sales 
---------+--------------------------+--------------------+-------------------
 Japan   |                     3742 |               3118 |                10
(1 row)

"Average monthly revenue" is £3,742 if empty months are ignored (NULL) and £3,118 if they count as zero. Both are correct answers to different questions. Decide which one the reader needs, and label it.

Step 4: the PIVOT keyword, in DuckDB

DuckDB can run the same pivot without writing the column list. Straight from the CSV:

sql

CREATE TABLE monthly_country AS
SELECT Country AS country,
       date_trunc('month', InvoiceDate)::DATE AS month,
       round(sum(Quantity * UnitPrice), 2)::DECIMAL(12,2) AS revenue
FROM read_csv('online-retail-dataset.csv', header = true,
              timestampformat = '%m/%d/%Y %H:%M')
WHERE Quantity > 0 AND UnitPrice > 0
GROUP BY ALL;

SELECT count(*) AS rows, sum(revenue) AS revenue FROM monthly_country;

PIVOT (SELECT country, strftime(month, '%Y-%m') AS ym, revenue
       FROM monthly_country
       WHERE country IN ('Japan', 'Australia') AND month >= DATE '2011-07-01')
ON ym
USING round(sum(revenue))
ORDER BY country;

Output

┌───────┬───────────────┐
│ rows  │    revenue    │
│ int64 │ decimal(38,2) │
├───────┼───────────────┤
│   302 │   10666684.54 │
└───────┴───────────────┘
 
┌───────────┬───────────────┬───────────────┬───────────────┬───────────────┬───────────────┐
│  country  │    2011-07    │    2011-08    │    2011-09    │    2011-10    │    2011-11    │
│  varchar  │ decimal(38,0) │ decimal(38,0) │ decimal(38,0) │ decimal(38,0) │ decimal(38,0) │
├───────────┼───────────────┼───────────────┼───────────────┼───────────────┼───────────────┤
│ Australia │          4964 │         22489 │          5107 │         17151 │          7243 │
│ Japan     │          3854 │          NULL │           112 │          1948 │          8460 │
└───────────┴───────────────┴───────────────┴───────────────┴───────────────┴───────────────┘

Same 302 rows and the same total as PostgreSQL. But look at the columns: there is no 2011-12, because neither Australia nor Japan bought anything in December 2011. A dynamic pivot only creates columns for values present in the filtered data, so next month's report could silently have a different shape. When the columns must be fixed, list them:

sql

PIVOT (SELECT country, strftime(month, '%Y-%m') AS ym, revenue
       FROM monthly_country
       WHERE country IN ('Japan', 'Australia') AND month >= DATE '2011-07-01')
ON ym IN ('2011-07', '2011-08', '2011-09', '2011-10', '2011-11', '2011-12')
USING round(sum(revenue))
ORDER BY country;

Output

┌───────────┬───────────────┬───────────────┬───────────────┬───────────────┬───────────────┬───────────────┐
│  country  │    2011-07    │    2011-08    │    2011-09    │    2011-10    │    2011-11    │    2011-12    │
│  varchar  │ decimal(38,0) │ decimal(38,0) │ decimal(38,0) │ decimal(38,0) │ decimal(38,0) │ decimal(38,0) │
├───────────┼───────────────┼───────────────┼───────────────┼───────────────┼───────────────┼───────────────┤
│ Australia │          4964 │         22489 │          5107 │         17151 │          7243 │          NULL │
│ Japan     │          3854 │          NULL │           112 │          1948 │          8460 │          NULL │
└───────────┴───────────────┴───────────────┴───────────────┴───────────────┴───────────────┴───────────────┘

Step 5: a dynamic pivot in PostgreSQL

PostgreSQL cannot discover columns, but it can write the query for you. format builds one FILTER expression per month, string_agg joins them in month order, and psql's \gexec runs the generated text as a query:

sql

SELECT format(
  'SELECT country, %s FROM monthly_country WHERE country IN (%L, %L) GROUP BY country ORDER BY country',
  string_agg(format('round(sum(revenue) FILTER (WHERE month = %L)) AS %I',
                    month, to_char(month, 'Mon YY')),
             ', ' ORDER BY month),
  'Japan', 'Australia')
FROM (SELECT DISTINCT month FROM monthly_country WHERE month >= DATE '2011-07-01') AS m
\gexec

Output

  country  | Jul 11 | Aug 11 | Sep 11 | Oct 11 | Nov 11 | Dec 11 
-----------+--------+--------+--------+--------+--------+--------
 Australia |   4964 |  22489 |   5107 |  17151 |   7243 |       
 Japan     |   3854 |        |    112 |   1948 |   8460 |       
(2 rows)

Here the months came from all countries, so Dec 11 exists. %L quotes values safely and %I quotes identifiers, which matters when the values come from data: never paste raw values into SQL text, or a product called x'); DROP TABLE ... becomes your problem. ORDER BY month inside string_agg keeps the columns in calendar order rather than alphabetical (Aug 11 before Jul 11).

Step 6: crosstab, and why teams avoid it

PostgreSQL's tablefunc extension has crosstab. You must spell out every output column and its type, and there are two versions:

sql

CREATE EXTENSION IF NOT EXISTS tablefunc;

-- two-argument form: the second query lists the categories
SELECT *
FROM crosstab(
  $$SELECT country, to_char(month, 'YYYY-MM'), round(revenue)
    FROM monthly_country
    WHERE country IN ('Japan', 'Australia') AND month >= DATE '2011-07-01'
    ORDER BY 1, 2$$,
  $$SELECT DISTINCT to_char(month, 'YYYY-MM') FROM monthly_country
    WHERE month >= DATE '2011-07-01' ORDER BY 1$$
) AS ct(country text, "2011-07" numeric, "2011-08" numeric, "2011-09" numeric,
        "2011-10" numeric, "2011-11" numeric, "2011-12" numeric);

-- one-argument form
SELECT *
FROM crosstab(
  $$SELECT country, to_char(month, 'YYYY-MM'), round(revenue)
    FROM monthly_country
    WHERE country IN ('Japan', 'Australia') AND month >= DATE '2011-07-01'
    ORDER BY 1, 2$$
) AS ct(country text, "2011-07" numeric, "2011-08" numeric, "2011-09" numeric,
        "2011-10" numeric, "2011-11" numeric, "2011-12" numeric);

Output

  country  | 2011-07 | 2011-08 | 2011-09 | 2011-10 | 2011-11 | 2011-12 
-----------+---------+---------+---------+---------+---------+---------
 Australia |    4964 |   22489 |    5107 |   17151 |    7243 |        
 Japan     |    3854 |         |     112 |    1948 |    8460 |        
(2 rows)
 
  country  | 2011-07 | 2011-08 | 2011-09 | 2011-10 | 2011-11 | 2011-12 
-----------+---------+---------+---------+---------+---------+---------
 Australia |    4964 |   22489 |    5107 |   17151 |    7243 |        
 Japan     |    3854 |     112 |    1948 |    8460 |         |        
(2 rows)

Look at Japan in the second result. The one-argument form fills columns left to right in the order rows arrive, ignoring the category value. Japan had no August row, so September's 112 slid into the 2011-08 column, October's value into September, and so on. No error, no warning, wrong numbers. That silent shift, plus needing an extension and a hand-written column list anyway, is why most teams use conditional aggregation instead.

Step 7: unpivot and prove the round trip

Build a wide table with all 13 months, then turn it back into long form and check that nothing was lost or invented.

sql

CREATE TABLE country_month_wide AS
SELECT country,
       sum(revenue) FILTER (WHERE month = DATE '2010-12-01') AS m2010_12,
       sum(revenue) FILTER (WHERE month = DATE '2011-01-01') AS m2011_01,
       sum(revenue) FILTER (WHERE month = DATE '2011-02-01') AS m2011_02,
       sum(revenue) FILTER (WHERE month = DATE '2011-03-01') AS m2011_03,
       sum(revenue) FILTER (WHERE month = DATE '2011-04-01') AS m2011_04,
       sum(revenue) FILTER (WHERE month = DATE '2011-05-01') AS m2011_05,
       sum(revenue) FILTER (WHERE month = DATE '2011-06-01') AS m2011_06,
       sum(revenue) FILTER (WHERE month = DATE '2011-07-01') AS m2011_07,
       sum(revenue) FILTER (WHERE month = DATE '2011-08-01') AS m2011_08,
       sum(revenue) FILTER (WHERE month = DATE '2011-09-01') AS m2011_09,
       sum(revenue) FILTER (WHERE month = DATE '2011-10-01') AS m2011_10,
       sum(revenue) FILTER (WHERE month = DATE '2011-11-01') AS m2011_11,
       sum(revenue) FILTER (WHERE month = DATE '2011-12-01') AS m2011_12
FROM monthly_country
GROUP BY country;

CREATE TABLE country_month_long AS
SELECT w.country, v.month, v.revenue
FROM country_month_wide AS w
CROSS JOIN LATERAL (VALUES
  (DATE '2010-12-01', w.m2010_12), (DATE '2011-01-01', w.m2011_01),
  (DATE '2011-02-01', w.m2011_02), (DATE '2011-03-01', w.m2011_03),
  (DATE '2011-04-01', w.m2011_04), (DATE '2011-05-01', w.m2011_05),
  (DATE '2011-06-01', w.m2011_06), (DATE '2011-07-01', w.m2011_07),
  (DATE '2011-08-01', w.m2011_08), (DATE '2011-09-01', w.m2011_09),
  (DATE '2011-10-01', w.m2011_10), (DATE '2011-11-01', w.m2011_11),
  (DATE '2011-12-01', w.m2011_12)
) AS v(month, revenue);

SELECT count(*) AS all_rows,
       count(revenue) AS non_null_rows,
       sum(revenue) AS revenue
FROM country_month_long;

Output

SELECT 38
SELECT 494
 all_rows | non_null_rows |   revenue   
----------+---------------+-------------
      494 |           302 | 10666684.54
(1 row)

The unpivot produced all 494 country-month cells, of which 302 hold a value: exactly the 302 rows we started with, and the same total to the penny. The 192 NULL cells are the empty months from step 1. Filter them out (WHERE v.revenue IS NOT NULL), as the UNPIVOT keyword does by default, or keep them if you need an explicit "no sales" row.

Matching totals can still hide swapped values, so compare row by row with EXCEPT in both directions:

sql

SELECT 'only in original' AS side, count(*) FROM (
  SELECT country, month, revenue FROM monthly_country
  EXCEPT
  SELECT country, month, revenue FROM country_month_long WHERE revenue IS NOT NULL) a
UNION ALL
SELECT 'only in round trip', count(*) FROM (
  SELECT country, month, revenue FROM country_month_long WHERE revenue IS NOT NULL
  EXCEPT
  SELECT country, month, revenue FROM monthly_country) b;

Output

        side        | count 
--------------------+-------
 only in original   |     0
 only in round trip |     0
(2 rows)

Zero both ways: every country, month and amount survived the trip. SQL UNION vs UNION ALL, INTERSECT and EXCEPT covers EXCEPT in detail.

Common mistakes

ELSE 0 by reflex. It turns "no data" into a real zero and changes averages (Japan: £3,742 vs £3,118).
Pivoting from a table with extra columns in SQL Server or Snowflake PIVOT. Every leftover column becomes part of the grouping.
Using SUM where there should be one value. If a student has two Physics rows, SUM adds them and MAX hides one. Check for duplicates first.
Trusting one-argument crosstab. Missing categories shift values into the wrong columns without an error.
Assuming a dynamic pivot has fixed columns. DuckDB's PIVOT dropped December when the filtered data had none.
Alphabetical column order from generated pivots. Use sortable labels (2011-07) or ORDER BY inside string_agg.
Building dynamic SQL by gluing raw values. Quote with format('%L') / %I, QUOTENAME in SQL Server.
Storing the wide table. Next month needs an ALTER TABLE. Keep the long table as the source of truth.
Forgetting that UNPIVOT drops NULLs, so row counts after a round trip look "wrong" when they are not.

Interview angle

Convert a table of (student, subject, marks) into one row per student with a column per subject.

Group by student and use one conditional aggregate per subject. Use MAX, not SUM, and leave missing subjects as NULL:

sql

WITH marks(student, subject, marks) AS (
  VALUES ('Aarav', 'Maths', 91), ('Aarav', 'Physics', 78), ('Aarav', 'Chemistry', 85),
         ('Diya', 'Maths', 88), ('Diya', 'Chemistry', 93),
         ('Kabir', 'Maths', 67), ('Kabir', 'Physics', 72), ('Kabir', 'Physics', 75)
)
SELECT student,
       max(CASE WHEN subject = 'Maths' THEN marks END)     AS maths,
       max(CASE WHEN subject = 'Physics' THEN marks END)   AS physics,
       max(CASE WHEN subject = 'Chemistry' THEN marks END) AS chemistry,
       count(*) AS rows_in
FROM marks
GROUP BY student
ORDER BY student;

Output

 student | maths | physics | chemistry | rows_in 
---------+-------+---------+-----------+---------
 Aarav   |    91 |      78 |        85 |       3
 Diya    |    88 |         |        93 |       2
 Kabir   |    67 |      75 |           |       3
(3 rows)

Then point out the two things an interviewer is looking for. Diya's Physics is NULL, not 0: she has no mark, and a 0 would drag her average down. And Kabir has two Physics rows (72 and 75); MAX quietly kept 75. Ask whether that is a retake (keep the latest, which needs a date) or a data error, and check with GROUP BY student, subject HAVING count(*) > 1 before pivoting.

How do you pivot when you don't know the column values in advance?

Plain SQL cannot, because the column list is part of the query. Generate the SQL: select the distinct values, build the conditional aggregates as a string (with safe quoting), and execute it (\gexec in psql, EXECUTE in a PL/pgSQL function, sp_executesql in SQL Server). Or use DuckDB's PIVOT or Snowflake's IN (ANY). Then mention that for dashboards the better answer is to return long data and let the BI tool pivot.

How do you unpivot in a database without UNPIVOT?

UNION ALL one SELECT per column, which works everywhere, or in PostgreSQL a CROSS JOIN LATERAL (VALUES ...), which reads the table once. Then prove it: row counts (remembering NULL cells), totals, and an EXCEPT in both directions.

Why is a long table better for storage?

New categories add rows, not columns, so the schema never changes. Filters, joins and aggregates work the same for every category. BI tools expect long data and pivot it themselves. Wide tables are for the final presentation step.

Practice task

Using Olist (CC BY-NC-SA 4.0), load orders, customers and payments as in the practice of SQL cohort analysis and customer retention.

Build a long table state_payments (state, payment_type, paid) with total payment value per customer state and payment type.
Pivot it into one row per state with columns credit_card, boleto, voucher, debit_card, not_defined, a total and the credit card share in percent. Show the six biggest states.
Look at the not_defined column. Why is it 0.00 for one state and empty for the rest?
Store the pivot as state_payments_wide, unpivot it back, and prove the row count, total and every row match the long table.

Show a solution

sql

CREATE TABLE state_payments AS
SELECT c.customer_state AS state, p.payment_type, sum(p.payment_value) AS paid
FROM payments AS p
JOIN orders AS o USING (order_id)
JOIN customers AS c USING (customer_id)
GROUP BY 1, 2;

SELECT count(*), sum(paid) FROM state_payments;

SELECT state,
       sum(paid) FILTER (WHERE payment_type = 'credit_card') AS credit_card,
       sum(paid) FILTER (WHERE payment_type = 'boleto') AS boleto,
       sum(paid) FILTER (WHERE payment_type = 'voucher') AS voucher,
       sum(paid) FILTER (WHERE payment_type = 'debit_card') AS debit_card,
       sum(paid) FILTER (WHERE payment_type = 'not_defined') AS not_defined,
       sum(paid) AS total,
       round(100 * sum(paid) FILTER (WHERE payment_type = 'credit_card') / sum(paid), 1) AS card_pct
FROM state_payments
GROUP BY state
ORDER BY total DESC
LIMIT 6;

Output

SELECT 106
 count |     sum     
-------+-------------
   106 | 16008872.12
(1 row)
 
 state | credit_card |   boleto   |  voucher  | debit_card | not_defined |   total    | card_pct 
-------+-------------+------------+-----------+------------+-------------+------------+----------
 SP    |  4676662.34 | 1084604.44 | 148301.67 |   88658.51 |        0.00 | 5998226.96 |     78.0
 RJ    |  1730340.68 |  328621.78 |  52994.22 |   32423.01 |             | 2144379.69 |     80.7
 MG    |  1471975.31 |  340518.70 |  37533.85 |   22229.40 |             | 1872257.26 |     78.6
 RS    |   662551.39 |  192327.91 |  21563.66 |   14455.58 |             |  890898.54 |     74.4
 PR    |   626606.74 |  155631.79 |  18375.61 |   10542.24 |             |  811156.38 |     77.2
 SC    |   481784.37 |  114313.98 |  19391.52 |    7596.56 |             |  623086.43 |     77.3
(6 rows)

São Paulo shows 0.00 under not_defined because the data has not_defined payment rows for SP worth 0.00 in total. Every other state has no such rows at all, so the cell is NULL. Same blank-looking business meaning, different facts: one says "payments exist with no value", the other "nothing exists". Credit cards carry 74% to 81% of the money in each of the six biggest states.

The round trip:

sql

CREATE TABLE state_payments_wide AS
SELECT state,
       sum(paid) FILTER (WHERE payment_type = 'credit_card') AS credit_card,
       sum(paid) FILTER (WHERE payment_type = 'boleto')      AS boleto,
       sum(paid) FILTER (WHERE payment_type = 'voucher')     AS voucher,
       sum(paid) FILTER (WHERE payment_type = 'debit_card')  AS debit_card,
       sum(paid) FILTER (WHERE payment_type = 'not_defined') AS not_defined
FROM state_payments
GROUP BY state;

WITH back AS (
  SELECT w.state, v.payment_type, v.paid
  FROM state_payments_wide AS w
  CROSS JOIN LATERAL (VALUES
    ('credit_card', w.credit_card), ('boleto', w.boleto), ('voucher', w.voucher),
    ('debit_card', w.debit_card), ('not_defined', w.not_defined)
  ) AS v(payment_type, paid)
  WHERE v.paid IS NOT NULL
)
SELECT (SELECT count(*) FROM state_payments) AS original_rows,
       (SELECT count(*) FROM back) AS round_trip_rows,
       (SELECT sum(paid) FROM state_payments) AS original_total,
       (SELECT sum(paid) FROM back) AS round_trip_total,
       (SELECT count(*) FROM (SELECT * FROM state_payments EXCEPT SELECT * FROM back) d) AS differences;

Output

SELECT 27
 original_rows | round_trip_rows | original_total | round_trip_total | differences 
---------------+-----------------+----------------+------------------+-------------
           106 |             106 |    16008872.12 |      16008872.12 |           0
(1 row)

27 states, 106 non-empty cells back out, the same total, and no differing rows. Because WHERE v.paid IS NOT NULL keeps SP's 0.00 but drops the NULLs, the not_defined row for SP survives the trip, which is exactly the distinction from question 3.

#Why it matters

#The concept

#Long vs wide

#Conditional aggregation: the pivot that works everywhere

#The PIVOT keyword

#Static vs dynamic pivots

#NULL vs 0 in empty cells

#Unpivot: wide back to long

#Column names

#Worked example: monthly revenue by country on Online Retail

#Step 1: build the long table

#Step 2: pivot with conditional aggregation

#Step 3: NULL or 0 for Japan's empty months?

#Step 4: the PIVOT keyword, in DuckDB

#Step 5: a dynamic pivot in PostgreSQL

#Step 6: crosstab, and why teams avoid it

#Step 7: unpivot and prove the round trip

#Common mistakes

#Interview angle

#Practice task

#Further reading

Why it matters

The concept

Long vs wide

Conditional aggregation: the pivot that works everywhere

The PIVOT keyword

Static vs dynamic pivots

NULL vs 0 in empty cells

Unpivot: wide back to long

Column names

Worked example: monthly revenue by country on Online Retail

Step 1: build the long table

Step 2: pivot with conditional aggregation

Step 3: NULL or 0 for Japan's empty months?

Step 4: the PIVOT keyword, in DuckDB

Step 5: a dynamic pivot in PostgreSQL

Step 6: crosstab, and why teams avoid it

Step 7: unpivot and prove the round trip

Common mistakes

Interview angle

Practice task

Further reading