How to format sql table using values as column-CodePudding

I have the following result :

-------------------------
| dept | Active request |
-------------------------
| AFG  | 3              |
| AGO  | 4              |
| KMN  | 1              |
| MOL  | 1              |
| POD  | 2              |
| SUD  | 2              |
-------------------------

How can I tranform it to have something like

--------------------------------------------------------------
| Title          | AFG | AGO | KMN | MOL | POD | SUD | TOTAL | 
--------------------------------------------------------------
| Active Request | 3   | 4   | 1   | 1   | 2   | 2   | 13    |
--------------------------------------------------------------

Here is my fiddle http://sqlfiddle.com/#!9/b51a03/3

CodePudding user response：

You could use a single pivot query:

SELECT
    'Active Request' AS Title,
    COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active;

CodePudding user response：

This can be done with the postgresql crosstab tablefunc.

You can find an detailed explanation at: Pivot Tables in PostgreSQL Using the Crosstab Function