Question 114799 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to format sql table using values as column

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

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    This can be done with the postgresql crosstab tablefunc.

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search