How to format sql table using values as column

Question

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

What are you actually trying to do? You can use PIVOT if you know the columns in advance, but that's no alternative to client-side formatting or reporting. SQL is a query language, not a report builder. A query's columns must be known at the time the query gets compiled. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 6, 2022 at 9:30
I have the following result ... you really should have included the data from your original table. It is not acceptable to just drop a Fiddle link. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 6, 2022 at 9:42

Tim Biegeleisen · Accepted Answer · 2022-09-06 09:41:45Z

1

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;

edited Sep 6, 2022 at 9:41

answered Sep 6, 2022 at 9:30

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

executable Over a year ago

I got "Active request" not found

Sibster · Accepted Answer · 2022-09-06 09:35:00Z

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

answered Sep 6, 2022 at 9:35

Sibster

3,2191 gold badge24 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to format sql table using values as column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related