CODEWITHSUNDEEP
sqlsql-serverpivot
gmoney
gmoney

Reputation: 365

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!

Upvotes: 0

Views: 44

Answers (1)

sgeddes
sgeddes

Reputation: 62831

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

Upvotes: 1

Related Questions