Postgres Multiple Rows as Single Row

Question

I know I need to use sub-queries for this, but I'm not sure how to go about it. I have multiple entries per column ID, but I want to display them as a single row. Here's the table design:

UUID | position_id | spot
-----+-------------+-----
111  |    1        | left
112  |    1        | right
113  |    3        | center
114  |    4        | right

The way I want to output this data is such:

postion_1_left | position_1_right | postion_3_center | position_4_right
---------------+------------------+------------------+-----------------
     true      |      true        |      true        |      true

The reason for this is that I want to put this data into a BIRT report, and having absolute values for each position_id and spot as true or false would make the report much nicer. The report would look as such:

             left  | center | right
-----------+-------+--------+-----------
position 1 | yes   |  no    |  yes
position 2 | no    |  no    |  no
position 3 | no    |  yes   |  no
position 4 | no    |  no    |  yes

I cannot think of a better way of doing this, so if anyone has a suggestion I'm open to it. Otherwise I'll proceed with this layout but I'm having a hard time coming up with the query. I tried starting with a query like this:

SELECT (SELECT spot FROM positions_table WHERE position_id = 3 AND spot = 'left')
from positions_table
WHERE uuid = 'afb36733'

But obviously that wouldn't work.

Answer 1

As you simple want to check if you have a given spot out of a finite list - ('left', 'center', 'right') - for each position_id, I see a very simple solution for your case using bool_or aggregation function (see also on SQL Fiddle):

SELECT
    pt.position_id,
    bool_or(pt.spot = 'left') AS left,
    bool_or(pt.spot = 'right') AS right,
    bool_or(pt.spot = 'center') AS center
FROM
    positions_table pt
GROUP BY
    pt.position_id
ORDER BY
    pt.position_id;

Result:

 position_id | left | right | center 
-------------+------+-------+--------
           1 | t    | t     | f
           3 | f    | f     | t
           4 | f    | t     | f
(3 rows)

You can then expand it with CASE to format better (or do that in your presentation layer):

SELECT
    pt.position_id,
    CASE WHEN bool_or(pt.spot = 'left') THEN 'yes' ELSE 'no' END AS left,
    CASE WHEN bool_or(pt.spot = 'right') THEN 'yes' ELSE 'no' END AS right,
    CASE WHEN bool_or(pt.spot = 'center') THEN 'yes' ELSE 'no' END AS center
FROM
    positions_table pt
GROUP BY
    pt.position_id
ORDER BY
    pt.position_id;

Result:

 position_id | left | right | center 
-------------+------+-------+--------
           1 | yes  | yes   | no
           3 | no   | no    | yes
           4 | no   | yes   | no
(3 rows)

Another common options of pivoting would be:

• using crosstab function from tablefunc
• using FILTER clause or CASE inside aggregation function

But as it is only true/false, bool_or seems more than enough here.