SQL merge several rows into Single row on matching value

user_12504850 0
Viewed 50

what I am looking to do is merge several rows of data to be displayed as a single row.

FROM THIS:

Contract ID Contract group ID Amount
140 189 90.00
140 190 85.00
140 191 300.00
151 190 10.00
152 189 200.00
152 191 50.00

TO THIS:

Contract ID Group 189 Group 190 Group 191 Total amount
140 90.00 85.00 300.00 475.00
151 00.00 10.00 00.00 10.00
152 200.00 00.00 50.00 50.00

Any suggestions would be most welcome.

Thanks.

aggregate-functions pivot sum mysql sql
1 Answers

You can use conditional aggregation:

select contractid,
    max(case when contractgroupid = 189 then amount else 0 end) as group_189,
    max(case when contractgroupid = 190 then amount else 0 end) as group_190,
    max(case when contractgroupid = 191 then amount else 0 end) as group_191,
    max(case when contractgroupid = 192 then amount else 0 end) as group_192,
    sum(amount) as total_amount
from mytable
group by contractid

If the same group may appear more than once for a given contract, you might want to use sum() instead of max().

user_106767160
Related