# Building a pivot table in SQLite

Suppose we have a `sales`

table with product incomes for the years 2020-2023:

```
┌─────────┬──────┬────────┐
│ product │ year │ income │
├─────────┼──────┼────────┤
│ alpha │ 2020 │ 100 │
│ alpha │ 2021 │ 120 │
│ alpha │ 2022 │ 130 │
│ alpha │ 2023 │ 140 │
│ beta │ 2020 │ 10 │
│ beta │ 2021 │ 20 │
│ beta │ 2022 │ 40 │
│ beta │ 2023 │ 80 │
│ gamma │ 2020 │ 80 │
│ gamma │ 2021 │ 75 │
│ gamma │ 2022 │ 78 │
│ gamma │ 2023 │ 80 │
└─────────┴──────┴────────┘
```

And we want to transform it into a so-called *pivot table*, where products serve as rows and years serve as columns:

```
┌─────────┬──────┬──────┬──────┬──────┐
│ product │ 2020 │ 2021 │ 2022 │ 2023 │
├─────────┼──────┼──────┼──────┼──────┤
│ alpha │ 100 │ 120 │ 130 │ 140 │
│ beta │ 10 │ 20 │ 40 │ 80 │
│ gamma │ 80 │ 75 │ 78 │ 80 │
└─────────┴──────┴──────┴──────┴──────┘
```

Some DBMS, like SQL Server, have a custom `pivot`

operator to do this. SQLite does not. Still, there are multiple ways to solve the problem. Let's examine them.

## 1. Filtered totals

Let's manually extract each year in a separate column and calculate a filtered total income for that year:

```
select
product,
sum(income) filter (where year = 2020) as "2020",
sum(income) filter (where year = 2021) as "2021",
sum(income) filter (where year = 2022) as "2022",
sum(income) filter (where year = 2023) as "2023"
from sales
group by product
order by product;
```

Here is our pivot table:

```
┌─────────┬──────┬──────┬──────┬──────┐
│ product │ 2020 │ 2021 │ 2022 │ 2023 │
├─────────┼──────┼──────┼──────┼──────┤
│ alpha │ 100 │ 120 │ 130 │ 140 │
│ beta │ 10 │ 20 │ 40 │ 80 │
│ gamma │ 80 │ 75 │ 78 │ 80 │
└─────────┴──────┴──────┴──────┴──────┘
```

This universal method works in every DBMS, not only SQLite. Even if your DB engine does not support `filter`

, you can always resort to using `case`

:

```
select
product,
sum(case when year = 2020 then income end) as "2020",
sum(case when year = 2021 then income end) as "2021",
sum(case when year = 2022 then income end) as "2022",
sum(case when year = 2023 then income end) as "2023"
from sales
group by product
order by product;
```

The `case`

statement is used here to include only income values for the `year`

that matches the specified year.

Using `filter`

is probably the easiest way when we know the columns in advance. But what if we don't?

## 2. Dynamic SQL

Let's build our query dynamically without hardcoding year values:

- Extract distinct year values.
- Generate a
`...filter (where year = X) as "X"`

query line for each year.

```
with years as (
select distinct year as year
from sales
),
lines as (
select 'select product ' as part
union all
select ', sum(income) filter (where year = ' || year || ') as "' || year || '" '
from years
union all
select 'from sales group by product order by product;'
)
select group_concat(part, '')
from lines;
```

This query returns the same SQL we wrote manually at the previous step (minus formatting):

```
select product , sum(income) filter (where year = 2020) as "2020" , sum(income) filter (where year = 2021) as "2021" , sum(income) filter (where year = 2022) as "2022" , sum(income) filter (where year = 2023) as "2023" from sales group by product order by product;
```

Now we have to execute it. For that, let's use the `eval(sql)`

function available as part of the `define`

extension:

```
select load_extension('./define');
with years as (
select distinct year as year
from sales
),
lines as (
select 'drop view if exists v_sales; ' as part
union all
select 'create view v_sales as '
union all
select 'select product '
union all
select ', sum(income) filter (where year = ' || year || ') as "' || year || '" '
from years
union all
select 'from sales group by product order by product;'
)
select eval(group_concat(part, ''))
from lines;
```

Here, we are building a `v_sales`

view which executes the query we've constructed previously. Let's select the data from it:

```
select * from v_sales;
```

```
┌─────────┬──────┬──────┬──────┬──────┐
│ product │ 2020 │ 2021 │ 2022 │ 2023 │
├─────────┼──────┼──────┼──────┼──────┤
│ alpha │ 100 │ 120 │ 130 │ 140 │
│ beta │ 10 │ 20 │ 40 │ 80 │
│ gamma │ 80 │ 75 │ 78 │ 80 │
└─────────┴──────┴──────┴──────┴──────┘
```

Note: extensions do not work in the playground, so you'll have to use your local SQLite to reproduce this step.

Nice!

## 3. Pivot extension

If dynamic SQL seems too much for you, there is a more straightforward solution — the `pivotvtab`

extension.

With it, we only have to provide three selects to build a pivot:

- Select row values.
- Select column values.
- Select cell values.

```
select load_extension('./pivotvtab');
create virtual table v_sales using pivot_vtab (
-- rows
(select distinct product from sales),
-- columns
(select distinct year, year from sales),
-- cells
(select sum(income) from sales where product = ?1 and year = ?2)
);
```

`pivot_vtab`

takes three arguments:

- A subquery that specifies the rows of the virtual table. In this case, the subquery selects all distinct products.
- A subquery that specifies the columns of the virtual table. In this case, the subquery selects all distinct years.
`pivot_vtab`

expects two values for each column, one for the column value and one for the column name. By duplicating the`year`

value, we are effectively using it as both the value and name for the column. - A subquery that specifies the values of the virtual table. In this case, the subquery selects the total
`income`

for a specific`product`

and`year`

combination. The`?1`

and`?2`

placeholders in the subquery represent parameters that will be filled in at runtime with the actual values for`product`

and`year`

.

The extension does the rest:

```
select * from v_sales;
```

```
┌─────────┬──────┬──────┬──────┬──────┐
│ product │ 2020 │ 2021 │ 2022 │ 2023 │
├─────────┼──────┼──────┼──────┼──────┤
│ alpha │ 100 │ 120 │ 130 │ 140 │
│ beta │ 10 │ 20 │ 40 │ 80 │
│ gamma │ 80 │ 75 │ 78 │ 80 │
└─────────┴──────┴──────┴──────┴──────┘
```

That's even easier than the `pivot`

operator in SQL Server!

## Summary

There are three ways to build a pivot table in SQLite:

- Using plain SQL and
`sum()`

+`filter`

expressions. - Building and executing a dynamic query with the
`eval()`

function. - Utilizing the
`pivotvtab`

extension.

──

P.S. Interested in using SQL for data analytics? Check out my book — SQL Window Functions Explained

★ Subscribe to keep up with new posts.