SQL pivoting and reshaping data: turning rows into columns and back

The same data can have different shapes

The way data is stored is not always the way we want to present it. Consider the following orders table, where each row represents one order:

Suppose we summarize the number of orders by customer and status with GROUP BY customer, status. The result is a long or tall shape, where the status values appear as rows:

SELECT
    customer,
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY
    customer,
    status
ORDER BY
    customer,
    status;

But a dashboard might want the status values as columns instead:

Now the status values have become columns. This transformation is called pivoting. The underlying information is similar; the shape of the result has changed.

What does pivoting mean?

A pivot usually takes values from one categorical column and turns those values into separate result columns. Three questions define the pivot:

Rows      -> what should one output row represent?   (customer)
Columns   -> which values should become columns?    (status)
Values    -> what value should fill those columns?  (order count)

If those three decisions are clear, the SQL becomes much easier to design.

Pivoting with conditional aggregation

A widely portable way to pivot data is conditional aggregation. We already learned patterns such as SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), which counts paid orders. We can create one expression for each status:

SELECT
    customer,
    SUM(CASE WHEN status = 'paid'      THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders
GROUP BY customer
ORDER BY customer;

The query still uses ordinary SQL concepts (GROUP BY, CASE, SUM). The pivot comes from assigning each category its own conditional aggregate.

How the pivot works

Take Ben’s rows, Ben | pending and Ben | paid. For the paid expression, they contribute pending -> 0 and paid -> 1, so paid_orders = 1. For the pending expression, they contribute pending -> 1 and paid -> 0, so pending_orders = 1. The GROUP BY customer keeps the output at one row per customer, and the conditional expressions decide which measure goes into each pivoted column.

Pivoting numeric measures

Pivoting is not limited to counts. To show paid, pending, and cancelled order value for each customer, define order value = quantity * unit_price and put it inside the CASE:

SELECT
    customer,
    SUM(CASE WHEN status = 'paid'      THEN quantity * unit_price ELSE 0 END) AS paid_order_value,
    SUM(CASE WHEN status = 'pending'   THEN quantity * unit_price ELSE 0 END) AS pending_order_value,
    SUM(CASE WHEN status = 'cancelled' THEN quantity * unit_price ELSE 0 END) AS cancelled_order_value
FROM orders
GROUP BY customer
ORDER BY customer;

The pivot dimensions remain rows by customer and columns by status, but the measure has changed from a COUNT of orders to a SUM of order value.

Pivoting without defining the correct grain

Suppose the requirement is return one row per customer with paid and pending order counts. Someone writes a valid query but adds product to the GROUP BY. Ben appears twice:

SELECT
    customer,
    product,
    SUM(CASE WHEN status = 'paid'    THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY
    customer,
    product;

-- A: conditional aggregation cannot be used with products
-- B: GROUP BY customer, product creates one row per customer-product
-- C: SQL automatically splits paid and pending rows
-- D: the data contains an invalid duplicate

The fix: group at the intended output grain

If one row should represent one customer, group by only customer:

SELECT
    customer,
    SUM(CASE WHEN status = 'paid'    THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

Why an aggregate is usually needed

Suppose Ben has several paid orders. If paid becomes one output column, SQL needs to know what single value to place in Ben’s paid column: a COUNT of paid orders, a SUM of paid order value, an AVG, a MAX, or a MIN. This is why pivoting usually involves an aggregate. The pivot converts several source rows into one value at an intersection such as Ben + paid, and the aggregation defines how those rows become one value.

Missing categories and zero vs NULL

Suppose Carla has no pending orders. With SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END), the result is 0, which makes sense because the measure is “number of pending orders” and zero means we know Carla has no pending orders. But another pivot may use MAX(CASE WHEN condition THEN some_value END); if no matching row exists, the result may be NULL.

Multiple measures in one pivoted result

Sometimes a report needs both counts and values, for example one row per customer with paid order count and paid order value. Because each column is its own conditional aggregate, you can place several metrics side by side in the same query:

SELECT
    customer,
    SUM(CASE WHEN status = 'paid'    THEN 1                    ELSE 0 END) AS paid_order_count,
    SUM(CASE WHEN status = 'paid'    THEN quantity * unit_price ELSE 0 END) AS paid_order_value,
    SUM(CASE WHEN status = 'pending' THEN 1                    ELSE 0 END) AS pending_order_count,
    SUM(CASE WHEN status = 'pending' THEN quantity * unit_price ELSE 0 END) AS pending_order_value
FROM orders
GROUP BY customer
ORDER BY customer;

The result now contains several metrics from the same source data. This is one reason conditional aggregation is so useful for reporting.

Native PIVOT syntax

Some SQL databases provide a dedicated PIVOT operator. For example, SQL Server supports a form conceptually similar to:

SELECT
    customer,
    [paid],
    [pending],
    [cancelled]
FROM (
    SELECT customer, status, order_id
    FROM orders
) AS source_data
PIVOT (
    COUNT(order_id)
    FOR status IN ([paid], [pending], [cancelled])
) AS p;

The exact syntax differs significantly across database systems. Some support PIVOT directly, some provide different pivoting functions, and some rely mainly on conditional aggregation. For a general SQL curriculum, the most important thing is to understand the transformation from row category to output column. Once that mental model is clear, learning a database-specific PIVOT syntax becomes much easier.

Conditional aggregation or PIVOT?

Both can solve similar problems. Conditional aggregation is explicit, easy to customize, works across many SQL systems, and supports several different measures easily. Native PIVOT can be shorter when many columns come from one categorical field, the database supports convenient pivot syntax, and the report structure is stable. Neither approach changes the fundamental question: which values become rows, which become columns, and what is calculated inside each cell?

Static vs dynamic pivot columns

Our query explicitly names paid, pending, cancelled, which assumes we know the possible categories in advance. If tomorrow a new status refunded appears, the existing query does not automatically create a refunded_orders column. This is a static pivot: the output columns are known when the query is written, which is often desirable for dashboards because the result schema stays predictable. When categories are not known in advance (product categories, survey questions, custom attributes, monthly periods), a dynamic pivot generates columns from the data itself, usually requiring database-specific SQL, procedural SQL, or query generation from an application.

Reshaping in the opposite direction

Pivoting usually means rows to columns. The reverse transformation, columns to rows, is often called unpivoting. Suppose we have a wide customer-order summary and want it long. A broadly understandable way to reshape columns into rows is UNION ALL:

SELECT customer, 'paid' AS status, paid_orders AS order_count
FROM customer_order_summary
UNION ALL
SELECT customer, 'pending' AS status, pending_orders AS order_count
FROM customer_order_summary
UNION ALL
SELECT customer, 'cancelled' AS status, cancelled_orders AS order_count
FROM customer_order_summary;

For each original customer row, we create three result rows, so Asha | 2 | 0 | 0 becomes three category-value rows. The grain has changed from one row per customer to one row per customer and status. Some database systems also provide a native UNPIVOT operator; the syntax varies, but understanding the shape change (customer | paid | pending to customer | status | value) matters more than memorizing one product’s implementation.

Wide vs long data

There is no universally better shape. Long format (customer | status | order_count) is easy to add categories to, often easier to aggregate, works naturally with GROUP BY, and usually suits analytical pipelines. Wide format (customer | paid | pending | cancelled) is easy for humans to scan, convenient for dashboards, useful when categories are fixed, and often matches spreadsheet-style reports.

Gotcha: pivoting can hide multiple source rows, and does not fix bad grain

How to spot pivoting and unpivoting

Think about pivoting when the requirement says one column per status, one column per month, show categories side by side, turn category values into report columns, create a dashboard matrix, or compare several measures across categories. Think about unpivoting when many similar columns need to become rows, a spreadsheet-style table needs analytical processing, column names themselves represent categories, or wide data needs to be normalized into category-value pairs.

Watch for these common mistakes:

• The GROUP BY creates more rows than the required output grain.
• The aggregate inside the pivot does not match the business metric.
• New categories appear but the static pivot does not include them.
• NULL is replaced with zero without confirming the meaning.
• A dynamic pivot creates an unstable output schema.
• Pivoting is expected to remove source duplicates.
• The result is made wide even though downstream analysis would be easier in long form.

Answer four questions before reshaping

Rows     What remains as the row grain?
Columns  Which category values become headings?
Values   What aggregate fills each intersection?
Schema   Are those columns known in advance?

One more prediction

Consider this long result. Requirement: return one row per customer with separate columns for paid, pending, and cancelled order counts. Which query best expresses the requirement?

-- A: SELECT customer, status, COUNT(*)
--    FROM orders GROUP BY customer, status

-- B: SELECT customer,
--      SUM(CASE WHEN status='paid'      THEN 1 ELSE 0 END) AS paid_orders,
--      SUM(CASE WHEN status='pending'   THEN 1 ELSE 0 END) AS pending_orders,
--      SUM(CASE WHEN status='cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
--    FROM orders GROUP BY customer

-- C: SELECT DISTINCT customer, status FROM orders

-- D: SELECT customer, COUNT(*) FROM orders GROUP BY customer

Summary

Pivoting changes the shape of a query result: long data (customer | status | count) can become wide data (customer | paid | pending). A broadly portable pivoting pattern uses conditional aggregation, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders, grouped at the output grain. The reverse transformation, unpivoting, turns columns back into category rows and can be expressed with database-specific UNPIVOT syntax or techniques such as UNION ALL.