下面是bigquery标准sql

# standardSQL

SELECT category, SUM(price) AS price FROM (
  SELECT category, price FROM `project.dataset.table`, 
  UNNEST(SPLIT(categories, ', ')) category UNION ALL
  SELECT 'Total', price FROM `project.dataset.table`
)
GROUP BY category

如果要应用于您问题中的样本数据，请参见下面的示例

# standardSQL

WITH `project.dataset.table` AS (
  SELECT 'Item1' AS item, 10 AS price, 'Cat1, Cat2'  AS categories UNION ALL
  SELECT 'Item2', 20, 'Cat1, Cat3' UNION ALL
  SELECT 'Item3', 15, 'Cat1, Cat2' 
)
SELECT category, SUM(price) AS price FROM (
  SELECT category, price FROM `project.dataset.table`, 
  UNNEST(SPLIT(categories, ', ')) category UNION ALL
  SELECT 'Total', price FROM `project.dataset.table`
)
GROUP BY category

结果是

Row category    price    
1   Cat1        45   
2   Cat2        25   
3   Cat3        20   
4   Total       45

总价值正确

您可以拆分数据，然后重新聚合：

with t as (
      select 'Item1' as item, 10 as price, 'Cat1, Cat2'  as categories union all
      select 'Item2', 20, 'Cat1, Cat3' union all
      select 'Item3', 15, 'Cat1, Cat2' union all
      select 'Total', 45, NULL
     )
select coalesce(category, 'Total'), sum(price)
from t cross join
     unnest(split(t.categories, ', ')) category
group by rollup(category)
order by category nulls last;

如果你不需要总数，那么去掉 rolllup .