我有一个名为test_table的表。这个表看起来像下面
id | 类型 | 值||
---|---|---|---|
1 | 税 | 10 | |
1 | 保费 | 21 | |
1 | 税 | 3 | |
1 | 校正 | 4.5 | |
2 | 保费 | 15 |
您可以尝试以下透视查询:
SELECT
id,
SUM(CASE WHEN type = 'premium' THEN value ELSE 0 END) AS premium,
SUM(CASE WHEN type = 'tax' THEN value ELSE 0 END) AS tax
SUM(CASE WHEN type = 'correction' THEN value ELSE 0 END) AS correction
FROM yourTable
GROUP BY id
ORDER BY id;
请注意,对于那些在源表中具有条目的单元格,上面将报告零。
在MS Sql Server中,PIVOT语法应该足够了。
select *
from (
select id, [type], value
from test_table
) src
pivot (
sum(value)
for [type] in ([premium], [tax], [correction])
) pvt
order by id