CREATE TABLE where columns' values are derived from BOOLEAN expression of columns´ in source table

Question

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

That was my previous question, reformulated here since it was not well understood there. — arilwan
– arilwan, Commented Sep 16, 2022 at 15:40

Saya · Accepted Answer · 2022-09-16 16:06:44Z

1

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.

answered Sep 16, 2022 at 16:06

Saya

3632 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

arilwan Over a year ago

Yes, Is there a way to cast the values from VARCHAR to BOOLEAN after transpose?

Saya Over a year ago

Yes, simply replace response with the response::BOOL and change the return types to BOOL. dbfiddle link: dbfiddle.uk/NxgHJHA-

Ramin Faracov · Accepted Answer · 2022-09-16 16:05:48Z

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

answered Sep 16, 2022 at 16:05

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

2 Comments

arilwan Over a year ago

I am wondering why the group by clause?

Ramin Faracov Over a year ago

@arilwan - without group by you will see unnecessary duplicate data, basically a separate line will appear for each response=1 and response_id will also show repeatedly

Collectives™ on Stack Overflow

CREATE TABLE where columns' values are derived from BOOLEAN expression of columns´ in source table

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related