I have a table called test_table. This table looks like below

I would like to "pivot" this table and make it look like below

• create columns by type (premium, tax and correction)
• sum the amounts by type and by id

With my basic sql knowledge, I have no idea how to build this query. Can you help me with this?

CodePudding user response：

You may try the following pivot query:

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;

Note that the above will report zero for those cells having entry in the source table.

CodePudding user response：

In MS Sql Server, the PIVOT syntax should be sufficiant for this.

select *
from (
  select id, [type], value
  from test_table
) src
pivot (
  sum(value) 
  for [type] in ([premium], [tax], [correction]) 
) pvt
order by id