postgres crosstab return 3 columns, how to add more column

Question

My question is similar to this but the answer didn't work in my case. Here's my table.

CREATE TABLE response(session_id int, seconds int, question_id int,
  response varchar(500),file bytea);

INSERT INTO response(session_id, seconds, question_id, response, file)
VALUES 
(652,1459721866,31,0,NULL),(652,1459721866,32,1,NULL),(652,1459721866,33,0,NULL),
(652,1459721866,34,0,NULL),(652,1459721866,35,0,NULL),(652,1459721866,36,0,NULL),
(652,1459721866,37,0,NULL),(652,1459721866,38,0,NULL),(656,1460845066,31,0,NULL),
(656,1460845066,32,0,NULL),(656,1460845066,33,0,NULL),(656,1460845066,34,0,NULL),
(656,1460845066,35,1,NULL),(656,1460845066,36,0,NULL),(656,1460845066,37,0,NULL),
(656,1460845066,38,0,NULL),(657,1463782666,31,0,NULL),(657,1463782666,32,0,NULL),
(657,1463782666,33,0,NULL),(657,1463782666,34,0,NULL),(657,1463782666,35,1,NULL),
(657,1463782666,36,0,NULL),(657,1463782666,37,0,NULL),(657,1463782666,38,0,NULL)

The following query works,

SELECT * FROM
    crosstab ('select session_id, question_id, response 
                from  response
     WHERE to_timestamp(seconds) BETWEEN ''2016-04-01'' AND ''2016-04-30''
                order by session_id,question_id') AS aresult (session_id int,
       --- seconds int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500));

Which gives:

session_id  not_moving  foot    bike    motor   car bus metro   train   other
652             0        1       0       0       0   0    0       0      null
656             0        0       0       0       1   0    0       0      null

But I need to return the seconds column as well. This query didn't work:

SELECT *
FROM  crosstab(
  $$ 
     SELECT session_id, seconds, question_id, response
     FROM response 
     WHERE to_timestamp(seconds) BETWEEN '2016-04-01' AND '2016-04-30'
     ORDER BY session_id, question_id
  $$,
  $$
   VALUES ('seconds')
  $$
  ) AS myresult (session_id int,
         seconds int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500)
  
  )
CROSS JOIN LATERAL age(seconds)

Expected output:

session_id  seconds   not_moving    foot    bike    motor   car bus metro   train   other
   652    1459721866     0           1       0        0      0   0    0       0      null
   656    1460845066     0           0       0        0      1   0    0       0      null

nbk · Accepted Answer · 2022-09-17 23:36:46Z

i am unsure what AGE supposed to be, but you can get, as i wrote by joing the sum of seconds

WITH CTE AS
(SELECT * FROM
    crosstab ('select session_id, question_id, response 
                from  response
     WHERE to_timestamp(seconds) BETWEEN ''2016-04-01'' AND ''2016-04-30''
                order by session_id,question_id') AS aresult (session_id int,
       --- seconds int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500))
  )
  SELECT CTE.session_id, seconds, not_moving, foot, bike, motor, car, bus,  metro, train, other
  FROM CTE 
  JOIN ( SELECT session_id,SUM(seconds) as seconds FROM response GROUP BY session_id) sum_sec 
  ON sum_sec.session_id = CTE.session_id
  
  ;

session_id	seconds	not_moving	foot	bike	motor	car	bus	metro	train	other
652	11677774928	0	1	0	0	0	0	0	0	null
656	11686760528	0	0	0	0	1	0	0	0	null

SELECT 2

fiddle

Collectives™ on Stack Overflow

postgres crosstab return 3 columns, how to add more column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related