I'm working on a database in LibreOffice Base and trying to output the equivalent of a pivot table. Base uses HSQL which, I understand, doesn't support pivot, but the reports you can make with it are perfect for me so I want to stick with it.

There are three columns I want to involve in my pivot: `Rotation`

, `Modality`

and `Number`

.

Here is an example of how the data looks at present:

```
Rotation | Modality | Number
1 | 1 | 5
1 | 2 | 3
1 | 3 | 4
2 | 1 | 6
2 | 1 | 5
2 | 3 | 2
3 | 1 | 1
3 | 2 | 4
```

As you can see, there are three modalities. You can have any number of modalities - zero or more per rotation.

And what I'm after (the sum of each modality per rotation):

```
Rotation | Modality 1 | Modality 2 | Modality 3
1 | 5 | 3 | 4
2 | 11 | 0 | 2
3 | 1 | 4 | 0
```

(I'd be equally happy with `Modality`

and `Rotation`

the other way around.) I've searched around a lot and have come up with two ways I think I should be able to achieve this: using `CASE WHEN`

and using subqueries.

**Trying CASE WHEN**

Here is what I've tried:

```
SELECT "Rotation"
,CASE "Modality" WHEN 1 THEN SUM( "Number" ) END
,CASE "Modality" WHEN 2 THEN SUM( "Number" ) END
,CASE "Modality" WHEN 3 THEN SUM( "Number" ) END
FROM "my database"
GROUP BY "Rotation"
ORDER BY "Rotation"
```

This query returns a table that looks like the one above except instead of showing the sum of each modality, it shows the sum for all modalities in that rotation in one (seemingly) random column, like this:

```
Rotation | Modality 1 | Modality 2 | Modality 3
1 | | | 12
2 | | | 13
3 | 5 | |
```

So something is wrong with the `SUM()`

component of the query, I just can't figure out what.

**Trying subqueries**

```
SELECT "Modality"
,( SELECT SUM( "Number" ) FROM "my database" WHERE "Rotation" = 1 AND "Modality" = 1 )
,( SELECT SUM( "Number" ) FROM "my database" WHERE "Rotation" = 2 AND "Modality" = 1 )
,( SELECT SUM( "Number" ) FROM "my database" WHERE "Rotation" = 3 AND "Modality" = 1 )
FROM "my database"
WHERE "Modality" = 1
GROUP BY "Modality"
```

That produces:

```
Modality | Rotation 1 | Rotation 2 | Rotation 3
1 | 5 | 11 | 1
```

Now obviously it only returns modality 1 due to the 'WHERE' clauses, but I can't figure out how to get all modalities displayed this subquery way. The subquery code also runs way slower than the `CASE WHEN`

, which makes me wonder if it's the wrong way to go about this.