我需要编写一个动态返回列的查询。例如,我有一个包含列的表 tblTest:
Id, Name, Type, Amount
1, Receipt, Cash 100
2, Receipt, Card 200
3, Receipt, Cheque 250
4, Receipt, Card 150
5, Receipt, Cash 100
6, Payment, Cash 300
7, Payment, Cheque 400
SQL查询:
SELECT
Name,
SUM(CASE WHEN Type = 'Cash' THEN Amount ELSE 0 END) Cash,
SUM(CASE WHEN Type = 'Card' THEN Amount ELSE 0 END) Card,
SUM(CASE WHEN Type = 'Cheque' THEN Amount ELSE 0 END) Cheque
FROM tblTest
GROUP BY
Name;
它返回我,
上面的结果是按照我的要求,但在我的情况下,上面数据中的类型现金、卡、支票等不是预定义的,它们可能是动态出现的,那么我如何管理它,请帮助我为此构建动态列 SQL。
提前致谢..