SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (
  `id` INTEGER,
  `item` VARCHAR(6),
  `totalcost` INTEGER,
  `sharedppl` INTEGER,
  `paidby` VARCHAR(5),
  `second` VARCHAR(5),
  `third` VARCHAR(5)
);
INSERT INTO tablename
  (`id`, `item`, `totalcost`, `sharedppl`, `paidby`, `second`, `third`)
VALUES
  ('1', 'Lunch', '150', '3', 'Tom', 'Jack', 'Harry'),
  ('2', 'Dinner', '200', '2', 'Jack', 'Harry', null),
  ('3', 'Drinks', '75', '3', 'Harry', 'Jack', 'Tom');
SELECT item,
       CASE WHEN 'Tom' IN (paidby, second, third) THEN 1.0 * totalcost / sharedppl ELSE 0 END AS Tom,
       CASE WHEN 'Jack' IN (paidby, second, third) THEN 1.0 * totalcost / sharedppl ELSE 0 END AS Jack,
       CASE WHEN 'Harry' IN (paidby, second, third) THEN 1.0 * totalcost / sharedppl ELSE 0 END AS Harry
FROM tablename


item
Tom
Jack
Harry


Lunch
50
50
50

Dinner
0
100
100

Drinks
25
25
25