Postgresql join same table

Question

I have table with fields:

line_id, point_address:
1 | Address1
1 | Address2
2 | Address1
2 | Address3
3 | Address2
3 | Address3

For result I need table with single row for each line_id and both point_address values line_id have in separate rows:

line_id,point_address_1,point_address_2:
1 | Address1 | Address2
2 | Address1 | Address3
3 | Address2 | Address3

I'm tried to create self join but what I got is 4 result for each line_id with all combinations of point_address fields. What is the best way to get single result

dhke · Accepted Answer · 2018-07-02 19:19:28Z

1

Assuming there are only ever two addresses per distinct line_id, you can work around by forcing an order on point_address in the result table:

SELECT
    l1.line_id, l1.point_address AS point_address1, 
        l2.point_address AS point_address2
    FROM line_address l1 JOIN line_Address l2 ON l1.line_id = l2.line_id
    WHERE l1.point_address < l2.point_address;

line_id     point_address1  point_address2
----------  --------------  --------------
1           Address1        Address2      
2           Address1        Address3      
3           Address2        Address3

Note that this only works under the above assumption, hence if we add

INSERT INTO line_address (line_id, point_address) VALUES (3, 'Address4');

the select yields

line_id     point_address1  point_address2
----------  --------------  --------------
1           Address1        Address2      
2           Address1        Address3      
3           Address2        Address3      
3           Address2        Address4      
3           Address3        Address4

Relational algebra does not take it kindly when you try to make rows into columns and vice versa.

answered Jul 2, 2018 at 19:19

dhke

15.5k2 gold badges42 silver badges60 bronze badges

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

1 Comment

Kajar Over a year ago

Yes, I have only two addresses per distinct line_id and this forcing an order on point_address worked great.

Collectives™ on Stack Overflow

Postgresql join same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related