CODEWITHSUNDEEP
sqlpostgresql
Suraj
Suraj

Reputation: 2477

Creating columns for each category in column

I am new to SQL. I have a table with data for different companies.

For example :

Company type count
--------------------
St1    sync    20
St2    async   30
St3    async   40
St3    sync    100
St1    async   90
St2    sync    65

Here, I'd like to print a report with sync and async as separate columns.

Company  sync   async
----------------------
St1      20     90
St2      65     30
St3      100    40

I've tried different queries, but can't really get the logic to implement it.

SELECT Company, 
(CASE WHEN type='async' THEN count END) as async,
(CASE WHEN type='sync' THEN count END) as sync,
FROM table
GROUP BY Company;

Included a query I've tried above.

Upvotes: 1

Views: 703

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522331

Use a pivot query:

SELECT
    Company,
    MAX(count) FILTER (WHERE type = 'sync')  AS sync,
    MAX(count) FILTER (WHERE type = 'async') AS async
FROM yourTable
GROUP BY
    Company
ORDER BY
    Company;

screen capture from demo link below

Demo

Upvotes: 3

Related Questions