skip to Main Content

My previous question was not understood well, so I reformulated it here. Having this as the source table:

CREATE TABLE response (response_id INT, utime INT, 
                     question_id INT, response VARCHAR(500))

INSERT INTO response (response_id,  utime, question_id, response)
VALUES (5,1459542506,11,1),(5,1459542506,12,0),(5,1459542506,13,0),
  (5,1459542506,14,0),(5,1459542506,15,0),(7,1458210291,11,0),(7,1458210291,12,1),
  (7,1458210291,13,1),(7,1458210291,14,0),(7,1458216077,15,1),(10,1458212391,11,1),
  (10,1458212391,12,0),(10,1458212391,13,1),(10,1458212391,14,0),(10,1458212391,15,0)

In the table are the results of users’ survey. So each user (response_id) has an answer to each question (question_id). There are 5 questions in all. The response to each question by the user is stored in response column. Response takes 1 of 2 possible values: a 0 or a 1 (unfortunately this was defined as VARCHAR in the source table).

The question_id stand for:

id      |  answer
--------+---------------
11      -  sitting
12      -  walking
13      -  standing
14      -  running
15      -  jogging    

Question

How do I create a table having say 6 columns, response_id, sitting, walking, standing, running, jogging with all columns (except the first as BOOLEAN type), so that sitting is assigned 1 or (true) if question_id=11 and response=1, otherwise 0 (false). Also walking is assigned 1/true if question_id=12 and response=1, and 0/false otherwise, etc.

Expected output

In the given example, I should have:

|response_id|sitting|walking|standing|running|jogging|
+-----------+-------+-------+--------+-------+-------+
|     5     |   1   |   0   |   0    |   0   |   0   |
+-----------+-------+-------+--------+-------+-------+
|     7     |   0   |   1   |   1    |   0   |   1   |
+-----------+-------+-------+--------+-------+-------+
|    10     |   1   |   0   |   1    |   0   |   0   |
+-----------+-------+-------+--------+-------+-------+  

2

Answers


  1. I wrote sample query for you:

    select 
        response_id, 
        max(case when question_id = 11 then 1 else 0 end) as "sitting", 
        max(case when question_id = 12 then 1 else 0 end) as "walking", 
        max(case when question_id = 13 then 1 else 0 end) as "standing", 
        max(case when question_id = 14 then 1 else 0 end) as "running", 
        max(case when question_id = 15 then 1 else 0 end) as "jogging" 
    from response
    where response = '1'
    group by response_id 
    
    Result: 
    response_id     sitting     walking     standing    running     jogging
    5               1           0           0           0           0
    7               0           1           1           0           1
    10              1           0           1           0           0
    
    Login or Signup to reply.
  2. It looks like you want to transpose the table. If so, then:

    SELECT * FROM
        crosstab ('select response_id, question_id, response from response order by 1,2') AS aresult (response_id int,
            sitting varchar(1),
            walking varchar(1),
            standing varchar(1),
            running varchar(1),
            jogging varchar(1));
    

    may help you.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search