CODEWITHSUNDEEP
sqlsql-serverpivot
Nassim
Nassim

Reputation: 117

How to pivot a table in sql and sum the amounts?

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

id type value
1 tax 10
1 premium 21
1 tax 3
1 correction 4.5
2 premium 15

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

id premium tax correction
1 21 13 (=10+3) 4.5
2 15 NULL NULL

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

Upvotes: 1

Views: 977

Answers (2)

LukStorms
LukStorms

Reputation: 29647

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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.

Upvotes: 2

Related Questions