Show multiple columns from a group of records

Question

Given the table:

score | name | time | nick
1       'A'    1      'X' 
4       'A'    2      'X'
10      'A'    3      'X'
10      'A'    4      'X'
1       'A'    5      'X'
1       'B'    6      'Z' 
4       'C'    7      'Z'

I'd like to create an output with multiple columns, first one being the score and the others the sum(time) as name||'_time_'::text||nick.

So it will be:

score | A_time_X | B_time_Z
1       6          0
4       2          0
10      7          0
1       0          6  
4       0          7

The columns for each score show the sum of time when the GROUP (score,name,nick) is the same. More columns will be shown for other values of nick.

Would that be possible?

Answer 1

The group by part is easy enough. After that, you're pivoting A_time_X and B_time_Z. One way to pivot is a case construct:

select  score
,       coalesce(case when name = 'A' and nick = 'X' then sum(time) end,0) as A_time_X
,       coalesce(case when name = 'B' and nick = 'Z' then sum(time) end,0) as B_time_Z
from    Table1
group by
        score
,       name
,       nick

Working example at SQL Fiddle.

For more pivoting options see this answer.

Answer 2

Poor man's pivot with CASE

I would aggregate in a subquery to simplify. Probably faster, too:

SELECT score
     , CASE WHEN name = 'A' AND nick = 'X' THEN sum_time ELSE 0 END AS a_x
     , CASE WHEN name = 'B' AND nick = 'Z' THEN sum_time ELSE 0 END AS b_z
FROM  (
   SELECT score, name, nick, COALESCE(sum(time), 0) AS sum_time
   FROM   table1
   GROUP  BY 1,2,3
   ) sub
ORDER  BY score, name, nick;

crosstab()

Read basic instructions here, first:

• PostgreSQL Crosstab Query

Since crosstab() expects a single column for the key value, generate a unique key from the combination of (name, nick):

SELECT * FROM crosstab(
   $$
   SELECT score
        , CASE WHEN name = 'A' AND nick = 'X' THEN 1
               WHEN name = 'B' AND nick = 'Z' THEN 2 END AS key
        , COALESCE(sum(time), 0) AS val
   FROM   table1
   WHERE (name = 'A' AND nick = 'X' OR
          name = 'B' AND nick = 'Z')   -- exclude rest (optional, faster)
   GROUP  BY name, nick, score
   ORDER  BY key, score;  -- (!) reversed order to get your untypical result
   $$
  ,'VALUES (1),(2)'       -- match key values in order
  ) AS t (score int, a_x int, b_z int);

Produces your result (except for your error in the last line).
Typically, however, you would want:

   ORDER  BY score, key;  -- (!) typical case

To get this result (all values for same score in one row):

score | A_time_X | B_time_Z
1       6          6
4       2          7
10      7          NULL

SQL Fiddle without crosstab() because it's currently not possible to install additional modules there.

Dynamic query?

Hard to come by, as @Andomar already comemnted. See what's possible here:

• Dynamic alternative to pivot with CASE and GROUP BY