CREATE TABLE where columns' values are derived from BOOLEAN expression of columns´ in source ta-CodePudding

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   |
 ----------- ------- ------- -------- ------- -------

CodePudding user response：

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

CodePudding user response：

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.