By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.record
(
food VARCHAR(100)
, food_preference VARCHAR(100)
, count INT
);
INSERT INTO record
VALUES
('burger', '[burger]', 100)
, ('burger', '[burger, pizza]', 70)
, ('pizza', '[burger, pizza]', 130)
, ('burger', '[burger, corn]', 25)
, ('corn', '[burger, corn]', 25);
5 rows affected
SELECT value AS food
, SUM(x.count) AS count
FROM record AS x
CROSS APPLY string_split(REPLACE(REPLACE(x.food_preference, '[', ''), ']', ''), ',') AS z
GROUP BY value;
| food | count |
|---|---|
| corn | 50 |
| pizza | 200 |
| burger | 350 |