Duplicate records

I am basically trying to used a stored procedure to update a table in 1 database based on a view in another. I have my view created fine, but the table that I am importing to has unique constraints that I have to comply to. I have duplicate records in my view that are unavoidable, so I need to rewrite my query to accommodate this.

Here is an example of the output from my view:



I need PARCEL_NBR to be unique, however, I cannot just remove duplicate records. This is because I need to maintain my acres value and zone value like this:



So I need to create a query that will generate the above table.

Basically it will be summing the acres and creating a new zone column and move he duplicate value there.

I would really appreciate it if anybody could help me.

You didn't mention what database or version you're using. Here's a solution that works in Oracle 10g.

Code:

select
  parcel_nbr
, sum( acres ) as acres
, max( decode( pivot_key, 1, zone, null ) ) as zone
, max( decode( pivot_key, 2, zone, null ) ) as zone_1
from
(
  select
    rank()
      over( partition by parcel_nbr order by zone )
    as pivot_key
  , parcel_nbr
  , acres
  , zone
  from t
)
group by
  parcel_nbr
;

PARCEL_NBR      ACRES ZONE       ZONE_1
---------- ---------- ---------- ----------
         1        1.2 I-1        PAD
         2          3 PAD
         3          1 AG-1

Thank you. This did exactly what I needed.