gmoney gmoney - 5 months ago 14
SQL Question

SQL Pivot with duplicates and same two first columns

Hello first time working with sql pivots . I have a table from a sql query like this below returning from the sql query.

Serial | SerialV2 | MeterType | MeterAmount
A1 | A11 | gas | 12
A1 | A11 | odometer | 1252
A2 | A22 | gas | 10
A2 | A22 | odometer | 105
A1 | A12 | gas | 1
A1 | A12 | odometer | 17542


I would like it to be this

Serial | SerialV2 | gas | odometer
A1 | A11 | 12 | 1252
A1 | A12 | 1 | 17542
A2 | A22 | 10 | 105


what I have so far written is

select serial, serialV2, [gas], [odometer]
from (
//sql query here
) query
PIVOT
(
sum(meterAmount)
FOR [meterType] in ([gas], [odometer])
) as p
order by serial


Will i have problem with the record that has the same serial number but not the V2 serial number?

Also, what would happen if a another dupllicate record showed up i.e. serial and serialV2 is the same?

Thanks!

Answer

Here's an alternative approach using conditional aggregation:

select serial, serialV2, 
    sum(case when metertype = 'gas' then meteramount end) as gas,
    sum(case when metertype = 'odometer' then meteramount end) as odometer
from (
   //sql query here 
) query
group by serial, serialV2