I have 2 tables that look something like this:
CREATE TEMP TABLE table_a ( ID_number_X text, ID_number_Y int ); INSERT INTO table_a VALUES ('HT59452', 123), ('HT59453', 123), ('HT59454', 123), ('HT59455', 789), ('HT59456', 101);
CREATE TEMP TABLE table_b ( Column_1 text, ID_number_Y int, ID_number_X text ); INSERT INTO table_b VALUES ('foo', 123, null), ('bar', 789, null), ('baz', 101, null);
I am running the following query to bring ID_number_X across to Table_B:
UPDATE table_b AS B SET id_number_x = A.id_number_x FROM table_a AS A WHERE B.id_number_y = A.id_number_y
This works fine, however, there are cases when there can be multiple results i.e. id_number_y can have more than one id_number_x associated with it.
How can I put those multiple results (when they occur) into additional columns?
So the result I want from the above example tables would be this:
Table B column_1 id_number_y id_number_x1 id_number_x2 id_number_x3 foo 123 HT59452 HT59453 HT59454 bar 789 HT59455 baz 101 HT59456
I think there could be up to 5 id_number_x for each id_number_y, so I can happily create the extra columns needed to store these.