Return to Question

I have 2 tables that look something like this:

Table A:

CREATE TEMP TABLE table_a (
  ID_number_X charvar,
  ID_number_Y int
);
INSERT INTO table_a VALUES
  (HT59452, 123),
  (HT59453, 123),
  (HT59454, 123),
  (HT59455, 789),
  (HT59456, 101);

Table B:

CREATE TEMP TABLE table_b (
  Column_1 text,
  ID_number_Y int,
  ID_number_X charvar
);
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.

I have 2 tables that look something like this:

Table A:

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);

Table B:

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.