I have a table tbl_action like this:

create table if not exists tblaction;

insert into "tblaction" (game_id, action_name, action_time) values (1,'start','2022-04-05T10:30 00'),
(2,'attack','2022-04-05T10:45 00'),
(3,'defend','2022-04-05T11:30 00'),
(4,'attack','2022-04-05T11:45 00'),
(5,'defend','2022-04-05T12:00 00'),
(6,'stop','2022-04-05T12:10 00'); 

I want to pivot it like this:

My question is how to aggregate actions 1..n instead of aggregating all of each unique action_name into one number. I am using Postgres.

This MySQL sample code works when each action_name occurs <2 times but I would like something that works 1..n. I don't want to remove the duplicates. I am not limited to MySQL I can use the latest versions of Postgres.

SELECT

 game_id, 

MAX( CASE WHEN action_name = 'start' THEN action_time ELSE NULL END ) AS "start",

MIN( CASE WHEN action_name = 'attack' THEN action_time ELSE NULL END ) AS "attack",

MIN( CASE WHEN action_name = 'defend' THEN action_time ELSE NULL END ) AS "defend",

MAX( CASE WHEN action_name = 'attack' THEN action_time ELSE NULL END ) AS "attack",

MAX( CASE WHEN action_name = 'defend' THEN action_time ELSE NULL END ) AS "defend",

MAX( CASE WHEN action_name = 'stop' THEN action_time ELSE NULL END ) AS "stop"

FROM

 tblaction

GROUP BY

 game_id

ORDER BY

 game_id ASC;

I read the Postgres tablefunc docs and tried \crosstabview in pgadmin4 and get the error \crosstabview: query result contains multiple data values for row "1", column "attack"

SELECT game_id, action_name, action_time FROM tblaction \crosstabview

CodePudding user response：

You can try to use subquery with ROW_NUMER window function which help you make row number by game_id, action_name columns, then you can use condition aggregate function for that rn

SELECT
    game_id, 
    MAX(CASE WHEN action_name = 'start'  THEN action_time   END ) AS "start",
    MAX(CASE WHEN action_name = 'attack' AND rn = 1 THEN action_time  END ) AS "attack1",
    MAX(CASE WHEN action_name = 'defend' AND rn = 1 THEN action_time  END ) AS "defend1",
    MAX(CASE WHEN action_name = 'attack' AND rn = 2 THEN action_time  END ) AS "attack",
    MAX(CASE WHEN action_name = 'defend' AND rn = 2 THEN action_time  END ) AS "defend",
    MAX(CASE WHEN action_name = 'stop'   THEN action_time   END ) AS "stop"
FROM
(
    SELECT *,ROW_NUMBER() OVER(PARTITION BY game_id,action_name ORDER BY action_time) rn
    FROM tblaction
) t1
GROUP BY
 game_id
ORDER BY
 game_id ASC;

sqlfiddle

CodePudding user response：

I suggest that it will be simpler and more flexible with GROUP_CONCAT

create table tblaction (
game_id int, 
action_name varchar(20), 
action_time varchar(25)
);

insert into tblaction 
(game_id, action_name, action_time) values 
(1,'start','2022-04-05T10:30 00'),
(1,'attack','2022-04-05T10:45 00'),
(1,'defend','2022-04-05T11:30 00'),
(1,'attack','2022-04-05T11:45 00'),
(1,'defend','2022-04-05T12:00 00'),
(1,'stop','2022-04-05T12:10 00'); 

✓

✓

SELECT
  game_id,
  GROUP_CONCAT(case when action_name='start' then action_time end) start,
  GROUP_CONCAT(case when action_name='stop' then action_time  end) stop,
  COUNT(action_time) "number",
  GROUP_CONCAT(case when action_name='attack' then action_time  end) "attacks",
  GROUP_CONCAT(case when action_name='defend' then action_time  end) "defends"
FROM tblaction
GROUP BY
  game_id;

game_id | start               | stop                | number | attacks                                 | defends                                
------: | :------------------ | :------------------ | -----: | :-------------------------------------- | :--------------------------------------
      1 | 2022-04-05T10:30 00 | 2022-04-05T12:10 00 |      6 | 2022-04-05T10:45 00,2022-04-05T11:45 00 | 2022-04-05T11:30 00,2022-04-05T12:00 00

db<>fiddle here