postgresql - 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

标签： postgresql

Answer 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.