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.
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:
But as it is only true/false, bool_or
seems more than enough here.