Tinashe - 8 months ago 26

SQL Question

I have a table that holds details for flats - contents of this table is similar to the following:

`| flat | description | Amount | Date`

--------------------------------------

| flat1 | electricity | 1 |1/1/2016

| flat1 | water | 2 |1/1/2016

| flat1 | levy | 3 |1/1/2016

| flat2 | electricity | 1 |1/1/2016

| flat2 | water | 2 |1/1/2016

| flat2 | levy | 3 |1/1/2016

I need a SQL view that can produce something like the following and any help would be appreciated:

`| Flat | electricity | water|levy | next description| and so on |`

---------------------------------------------------------------

| flat1 | 1 | 2 | 3 | next amount | next amount |

| flat2 | 1 | 2 | 3 | next amount | next amount |

Answer

This is called table `pivoting`

. Here's one option using `conditional aggregation`

assuming you know the number of potential columns:

```
select flat,
max(case when description = 'electricity' then amount end) electricity,
max(case when description = 'water' then amount end) water,
max(case when description = 'levy' then amount end) levy
from yourtable
group by flat
```

If you don't know the maximum number of columns, look up `dynamic pivot`

-- there are lots of examples on how to do it: