Tinashe Tinashe - 4 months ago 13
SQL Question

Converting multiple rows to columns with heading from the same row

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: