Given this original table Record
:
food | food_preference | count
--------------------------------
burger | [burger] | 100
burger | [burger, pizza] | 70
pizza | [burger, pizza] | 130
burger | [burger, corn] | 25
corn | [burger, corn] | 25
How do I iterate through all unique food
values to sum the count of food
in food_preference
to give:
food | count
--------------
burger | 350
pizza | 200
corn | 50
I tried to do a case matching but not sure how to iterate through the food values.
WITH Record AS
(
SELECT * FROM (
values
('burger', '[burger]', 100),
('burger', '[burger, pizza]', 70),
('pizza', '[burger, pizza]', 130),
('burger', '[burger, corn]', 25),
('corn', '[burger, corn]', 25)
) x(food, food_preference, count)
)
SELECT
food,
CASE
-- How to sum the count value if the food is in the food_preference?
END AS total_count
FROM Record
Using STRING_SPLIT
will do what you want.
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;