# Pivot Tables in PostgreSQL

To use the pivot table function you need the tablefunc extention for postgres.

```CREATE extension tablefunc;
```

I liked this post : vertabelo post it helped me but I want to add to that one, so please read it first. Then come back.
So consider this SQL

```select IDENTIFIER, CATAGORIES, SUM(value) as VALUES
from TABLE
group by IDENTIFIER, CATAGORIES
```

So lets consider our categories being 3 letter Months ie (Jan, Feb,….) and we want to show payments for each month.

```select SOME_PRIMARY_KEY,
SUM(PAYMENTS) as TOT_PAID
from TABLE
group by SOME_PRIMARY_KEY, to_char (DATE_PAYMENT_MADE,  ''Mon'')
```

Now to run this have each month as a column we would

``` SELECT *   FROM crosstab( '
select SOME_PRIMARY_KEY,
SUM(PAYMENTS) as TOT_PAID
from TABLE
group by SOME_PRIMARY_KEY, to_char (DATE_PAYMENT_MADE,  ''Mon'')
')
AS final_result(MyPK character varying(20) , Jan numeric,Feb numeric,Mar numeric,Apr numeric,May numeric,Jun numeric,Jul numeric,Aug numeric,Sep numeric, Oct numeric, Nov numeric,Dec numeric)
)
```

This does not give us the desired result since it will fill up from the fist column the values and not assign it to the correct column. To get the desired result we will have to add a second parameter to crosstab

``` SELECT *   FROM crosstab( '
select SOME_PRIMARY_KEY,
{"ft_lang_label":"__label__en","ft_lang_prob":0.70503265,"math_prob":0.80906266,"size":1823,"snap":"2019-43-2019-47","text_gpt3_token_len":468,"char_repetition_ratio":0.1588785,"word_repetition_ratio":0.35833332,"special_character_ratio":0.25946242,"punctuation_ratio":0.19648094,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9531539,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-11-12T07:50:19Z\",\"WARC-Record-ID\":\"<urn:uuid:5a0fb64e-a1ff-4bb8-9e51-ca0ebef39b9b>\",\"Content-Length\":\"77858\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:f7911dac-8199-4e07-a40a-69385d9bc607>\",\"WARC-Concurrent-To\":\"<urn:uuid:bea9e235-f9a3-4142-8da6-af87a2581ef8>\",\"WARC-IP-Address\":\"164.160.91.11\",\"WARC-Target-URI\":\"http://www.stepit.co.za/wp/blog/2018/02/16/pivot-tables-in-postgresql/\",\"WARC-Payload-Digest\":\"sha1:4SYCFUMJ6ZAUQUBPYMHSUDYKX6G4PM5U\",\"WARC-Block-Digest\":\"sha1:L7YVOSIIEZMYMDSOYMEDDLWQMKQUDWV7\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-47/CC-MAIN-2019-47_segments_1573496664808.68_warc_CC-MAIN-20191112074214-20191112102214-00149.warc.gz\"}"}