CodeRang
Common problems

SQL Pivot: How to Convert Rows to Columns

SQL pivot converts category values stored in rows into separate result columns. This example shows how to build a pivot-style table with conditional aggregation and FILTER.

Updated · SQL Arena team

SQL from zero → Analyst trackAll problems

What you will practice

  • Turn known row categories into columns
  • Use PostgreSQL aggregate FILTER
  • Build a portable pivot with SUM(CASE ...)
  • Choose deliberately between NULL and zero
  • Reverse a fixed pivot with UNION ALL

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Pivot sales channels into report columns

For every shop, return shop_name, web revenue as web_sales, and in-store revenue as store_sales. Keep shops even when they have no matching sales, and display missing channel totals as 0.

Query 1PostgreSQLv16
SELECT s.shop_name,
       COALESCE(SUM(sa.amount) FILTER (WHERE sa.channel = 'web'), 0) AS web_sales,
       COALESCE(SUM(sa.amount) FILTER (WHERE sa.channel = 'store'), 0) AS store_sales
FROM shops s
LEFT JOIN sales sa ON sa.shop_id = s.id
GROUP BY s.id, s.shop_name
ORDER BY s.shop_name;
Submit
Resultshopssales
Answer accepted

How SQL Pivot Works Step by Step

The sales table is in long format: the channel is stored as a row value such as web, store, or partner. The report needs a wide format where web and store are separate columns. PostgreSQL's FILTER syntax makes that concise: SUM(amount) performs the aggregation, while FILTER decides which rows are allowed into that particular sum.

Because the report starts from shops and keeps every shop, some groups have no matching rows for one of the requested channels. SUM then produces NULL for that measure. COALESCE changes the missing aggregate to 0, which is appropriate here because the report defines the value as sales through that channel. Harbor therefore gets zero web sales, and Market gets zero in both requested columns.

The same rows-to-columns query can be written with SUM(CASE WHEN channel = 'web' THEN amount ELSE 0 END). CASE is useful when you want a pattern that transfers easily across SQL dialects. Both approaches assume that the output columns are known in advance. The query explicitly defines web_sales and store_sales rather than discovering new columns at runtime.

SQLPostgreSQL v16
SELECT s.shop_name,
       COALESCE(SUM(sa.amount) FILTER (WHERE sa.channel = 'web'), 0) AS web_sales,
       COALESCE(SUM(sa.amount) FILTER (WHERE sa.channel = 'store'), 0) AS store_sales
FROM shops s
LEFT JOIN sales sa ON sa.shop_id = s.id
GROUP BY s.id, s.shop_name
ORDER BY s.shop_name;
Query result
shop_nameweb_salesstore_sales
Central90110
Harbor0150
Market00
North12080
Park700

Common SQL Pivot Mistakes

01

Returning NULL when the report expects zero

Conditional aggregates can return NULL when no matching rows exist. If the business meaning is 'there were zero sales', wrap the aggregate in COALESCE or use an appropriate ELSE branch in CASE.

02

Expecting static SQL to create arbitrary columns

A regular SELECT has a fixed output shape. SUM(CASE...) and FILTER work well when values such as web and store are known before the query is written. Truly dynamic categories usually require generated SQL or a long-format result instead.

03

Replacing every NULL with zero automatically

NULL can mean unknown, unavailable, or not applicable, while zero is an actual numeric value. A pivot query should only apply COALESCE when those meanings are genuinely interchangeable for the report.

Practice problems on this topic

SQL from zero → Analyst track

Once the basic pivot pattern is clear, apply it to practical scorecards: split payment totals by payment method, calculate account balances from transaction types, or build delivery metrics by store. SQL Arena includes free Easy tasks that use these patterns, while more complex exercises extend them to larger analytical queries. For a structured route from fundamentals to analyst-style SQL, continue with the SQL Beginner → Analyst track.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL pivot?

SQL pivot is a pattern that turns category values from rows into separate columns. It is commonly used to build compact reports and summary tables with one row per entity or group.

How do you pivot rows to columns in SQL?

The exact syntax depends on the database. You can use a dedicated PIVOT operator where available, or use conditional aggregation with SUM, COUNT, CASE, or FILTER to calculate each output column.

How do you create a pivot table in SQL?

Choose the field that defines each result row, then create one aggregate expression for every category you want as a column. A GROUP BY combines the source rows while the conditional aggregates populate the pivoted columns.

Can you pivot in SQL without a PIVOT operator?

Yes. Conditional aggregation is a common alternative and works by calculating separate aggregates for different category values. Depending on the SQL dialect, those conditions can be written with CASE expressions or FILTER clauses.

See also

Practice SQL Pivot Queries

SQL Arena contains 1,192 automatically checked tasks, with separate browser sandboxes for PostgreSQL, MySQL, ClickHouse, and BigQuery. The free plan needs no card and includes 292 tasks across all four dialects.

Practice in SQL Arena

More practice from CodeRang