SQL - aggregation with column value as column name

Question

For a table like below need to do an aggregation such that for each unique field in one column, need to find the count of occurrences of a discrete value in another column

input table is:

id  model  datetime     driver   distance
---|-----|------------|--------|---------
1  | S   | 04/03/2009 | john   | 399 
2  | X   | 04/03/2009 | juliet | 244
3  | 3   | 04/03/2009 | borat  | 555
4  | 3   | 03/03/2009 | john   | 300
5  | X   | 03/03/2009 | juliet | 200
6  | X   | 03/03/2009 | borat  | 500
7  | S   | 24/12/2008 | borat  | 600
8  | X   | 01/01/2009 | borat  | 700

Output required

model  john    juliet | borat
-----|--------|-------|------
 S   | 1      | 0     |  1
 X   | 0      | 2     |  2
 3   | 1      | 0     |  1

one potential way to do is to group by model with an aggregation like SUM (CASE WHEN driver = 'value' THEN 1 ELSE 0 END) AS value for each discrete value of driver column. But the challenge is sometimes the number of discrete values is too many ( around 50 in my case) or in some cases do not even know all possible discrete values - I was wondering if there is an alternate way to do this.

Possible duplicate of [Dynamic pivot query using PostgreSQL 9.3](http://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3) — Juan Carlos Oropeza, Sep 14 '16 at 14:22

score 0 · Answer 1 · edited May 23 '17 at 12:15

The aggregation part need a litle more work.

Here the details:

Need calculate first what are all the combinations
Then use LEFT JOIN to get which combination doesnt have data.

DEMO

WITH "allDrivers" as (
    SELECT DISTINCT "driver"
    FROM Table1
), 
"allModels" as (
    SELECT DISTINCT "model"
    FROM Table1
), 
"source" as (
    SELECT d."driver", m."model"
    FROM "allDrivers" d
    CROSS JOIN "allModels" m
)  
SELECT s."model", s."driver", COUNT(t."datetime")
FROM "source" s 
LEFT JOIN table1 t
       ON s."model"  = t."model"
      AND s."driver" = t."driver"
GROUP BY s."model", s."driver"

OUTPUT

| model | driver | count |
|-------|--------|-------|
|     3 |  borat |     1 |
|     3 |   john |     1 |
|     3 | juliet |     0 |
|     S |  borat |     1 |
|     S |   john |     1 |
|     S | juliet |     0 |
|     X |  borat |     2 |
|     X |   john |     0 |
|     X | juliet |     2 |

Then you can do the dynamic pivot

could you try my answer? – Juan Carlos Oropeza Sep 16 '16 at 17:06 — Juan Carlos Oropeza, Sep 16 '16 at 17:06

SQL - aggregation with column value as column name

input table is:

Output required

1 Answers1