How can I Select 1st, 2nd and 3rd values in different columns – Ms Access

I have this data in a table:

 ID     ItemID    ItemSupplier
 1        1           E
 2        2           E
 3        2           B
 4        3           B
 5        4           B
 6        4           E
 7        4           C
 8        5         'NULL'
 9        6           C

I would like to write a Query to select it as such:

ItemID    Supplier1    Supplier2    Supplier3 
  1           E
  2           E            B
  3           B
  4           B            E            C
  5           
  6           C

But I can only get the first column with :

SELECT ItemID, FIRST(ItemSupplier) AS Supplier1
FROM myTable GROUP BY ItemID

Thank you

Answer

MS Access is not the best tool for this.

One method uses a correlated subquery to enumerate the values and then conditional aggregation:

select itemid,
       max(iif(seqnum = 1, itemsupplier, null)) as supplier_1,
       max(iif(seqnum = 2, itemsupplier, null)) as supplier_2,
       max(iif(seqnum = 3, itemsupplier, null)) as supplier_3
from (select t.*,
             (select count(*)
              from t as t2
              where t2.itemid = t.itemid and t2.id <= t.id
             ) as seqnum
      from t
     ) as t
group by itemid;

Almost any other database supports window functions, which would make this much more efficient.