How to use one select query to return a restructured table?

Question

For example, I have this table:

+------+--------+-------+--------+-------+
| name | length | width | status | side  |
+------+--------+-------+--------+-------+
| T1   | 5      | 2     | DONE   | Left  |
+------+--------+-------+--------+-------+
| T1   | 2      | 1     | DONE   | Right |
+------+--------+-------+--------+-------+

But need one select query to return:

 +------+-------------+------------+-------------+--------------+-------------+--------------+
| name | left_length | left_width | left_status | right_length | right_width | right_status |
+------+-------------+------------+-------------+--------------+-------------+--------------+
| T1   | 5           | 2          | DONE        | 2            | 1           | DONE         |
+------+-------------+------------+-------------+--------------+-------------+--------------+

What is this kind of operation called? I'm using Postgres.

You can use the conditional aggregation solution provided in the answer, or you can install the tablefunc module and use the crosstab function, as described here: http://stackoverflow.com/questions/3002499/postgresql-crosstab-query — cha, May 11 '17 at 04:12
Thanks for the terminology. I had no idea what to look-up. I see I have much to read now! — Tony Clifton, May 11 '17 at 04:18

Gurwinder Singh · Accepted Answer · 2017-05-11T04:07:09.870

Here is one way of doing it using conditional aggregation:

select name,
    max(case when side = 'Left' then length end) as left_length,
    max(case when side = 'Left' then width end) as left_width,
    max(case when side = 'Left' then status end) as left_status,
    max(case when side = 'Right' then length end) as right_length,
    max(case when side = 'Right' then width end) as right_width,
    max(case when side = 'Right' then status end) as right_status
from your_table
group by name;

How to use one select query to return a restructured table?

1 Answers1