sql - Consolidate, Combine, Merge Rows -

every search leads me results people seeking array_agg combine multiple columns in row column. that's not trying figure out here, , maybe not using right search terms (e.g., consolidate, combine, merge).

i trying combine rows populating values in fields ... not sure best way describe other example:

current: --------------------------------  id  num_1  num_2  num_3  num_4  --------------------------------  1    111    222     0      0     2    111    333     0      0     3    111     0      0     444    4     0     222    555     0     5    777    999     0      0     6     0     999    888     0     after processing: --------------------------------  id  num_1  num_2  num_3  num_4  --------------------------------  1    111    222    555    444    2    111    333    555    444    3    111    333    555    444    4    111    222    555    444    5    777    999    888     0     6    777    999    888     0      after deleting duplicate rows: --------------------------------  id  num_1  num_2  num_3  num_4  --------------------------------  1    111    222    555    444    2    111    333    555    444    3    777    999    888     0    

this 2 step process ... first fill in blanks, , find/delete duplicate. can second step, having trouble figuring how first populate 0 values values row might have 2 different values (id 1/2 num_2 column) 1 value num_1 (e.g., 111)

i can in php, figure out how using postgres.

edit: example table relations table. have multiple datasets similar information (e.g., username) different registration id numbers. so, inner join on table 1 , table 2 (for example) username same. take registration ids (which different) each table , insert row relations table. in example tables above, row 1 has 2 different registration ids 2 tables joined … values 111 (num_1) , 222 (num_2) inserted table , zeros inserted num_3 , num_4. compare table 1 , table 4 , values 111 (num_1) , 444 (num_4) inserted relations table , zeros num_2 , num_3. since registration id 111 related registration id 222 , registration id 111 related registration id 444, registration ids 111, 222, , 444 related (meaning username same each of registration ids). clarify?

edit 2: corrected tables 2 , 3. makes sense. username column not unique. so, have 4 tables this:

table 1:

bob  - 111 mary - 777 

table 2:

bob  - 222 bob  - 333 mary - 999 

table 3:

bob  - 555 mary - 888 

table 4:

bob  - 444  -- mary not exist in table 

so, in relations table should end 3 rows given in example table 3 above.

it seems started in middle of presumed solution, forgetting present initial problem. based on added information suggest different, simpler solution. have:

create table table1 (username text, registration_id int); create table table2 (like table1); create table table3 (like table1); create table table4 (like table1);  insert table1 values ('bob', 111), ('mary', 777); insert table2 values ('bob', 222), ('bob', 333), ('mary', 999); insert table3 values ('bob', 555), ('mary', 888); insert table4 values ('bob', 444); -- no mary 

solution

what really seem need full [outer] join. details in manual on from , join.

-- create table relations select username      , t1.registration_id reg1      , t2.registration_id reg2      , t3.registration_id reg3      , t4.registration_id reg4   table1     t1 full   join table2 t2 using (username) full   join table3 t3 using (username) full   join table4 t4 using (username) order  username; 

that's all. produces desired result directly.

username  reg1  reg2  reg3  reg4 --------------------------------- bob       111   222   555   444 bob       111   333   555   444 mary      777   999   888   (null) 

your given example work left join well, since missing entries right. fail in other constellations. added more revealing test cases in fiddle:

sql fiddle.

i assume aware multiple entries in multiple tables produce huge number of output rows:

• two sql left joins produce incorrect result