How to convert rows from SQL query to columns

Question

I have the SQL query which generates a table with users and their answers for the task numbers:

Current result

Which SQL query should I use to get something like this:

Required result

I have 50 question numbers and about 1500 users. Unfortunately, it seems to me that simple SELECT and GROUP BY cannot do here.

Thank you very much!

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Nov 25, 2020 at 18:43

Gordon Linoff · Accepted Answer · 2020-11-25 18:44:42Z

1

You can use conditional aggregation:

select username,
       max(answer) filter (where task_number = 1) as task_1,
       max(answer) filter (where task_number = 2) as task_2
from t
group by username;

answered Nov 25, 2020 at 18:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

evermake Over a year ago

It works! Thank you very much! But, could you explain why we should use max(answers)?

Gordon Linoff Over a year ago

@VladDeryabkin . . . There is only one row that matches the filtering conditions. You need an aggregation function, so max() is as good as any such function.

Collectives™ on Stack Overflow

How to convert rows from SQL query to columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related