如何通过小计算从现有表创建新的sql表

Question

我有一个类似这样的SQLite表：

>第二个第三个>杰克哈利

id	项目	totalcost	sharedppl
1	午餐	150		2		晚餐	200
3	饮料	75

使用CASE表达式计算每个人的份额：

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

请参阅演示

一种方法，使用unpivot，后跟pivot和聚合：

WITH cte AS (
SELECT item, totalcost, paidby AS person FROM yourTable
UNION ALL
SELECT item, totalcost, second FROM yourTable
UNION ALL
SELECT item, totalcost, third FROM yourTable
)
SELECT
item,
CASE WHEN COUNT(CASE WHEN person = 'Tom' THEN 1 END) > 0
THEN MAX(totalcost) / COUNT(person) ELSE 0 END AS Tom,
CASE WHEN COUNT(CASE WHEN person = 'Jack' THEN 1 END) > 0
THEN MAX(totalcost) / COUNT(person) ELSE 0 END AS Jack,
CASE WHEN COUNT(CASE WHEN person = 'Harry' THEN 1 END) > 0
THEN MAX(totalcost) / COUNT(person) ELSE 0 END AS Harry
FROM cte
GROUP BY item, id
ORDER BY id;

附带说明：您当前的表设计没有正确规范化。您可以也应该在此处将其更改为您想要的输出。