Oracle - produce unique rows for each unique column value and convert rows to columns

squashbuff

Oracle 11g R2 is in use. This is my source table:

ASSETNUM    WONUM   WODATE     TYPE1    TYPE2   LOCATION
--------------------------------------------------------
W1          1001    2015-10-10  N       N       loc1
W1          1002    2015-10-02  Y       N       loc2
W1          1003    2015-10-04  Y       N       loc2
W1          1004    2015-10-05  N       Y       loc2
W1          1005    2015-10-07  N       Y       loc2
W2          2001    2015-10-11  N       N       loc1
W2          2002    2015-10-03  Y       N       loc2
W2          2003    2015-10-02  Y       N       loc2
W2          2004    2015-10-08  N       Y       loc3
W2          2005    2015-10-06  N       Y       loc3

http://sqlfiddle.com/#!4/8ee297/1

I want to write a query to get following data:

ASSETNUM    LATEST      LOCATION for   LATEST_WODATE_FOR   LATEST_WODATE_FOR    
            WODATE      LATEST WODATE   TYPE1=Y             TYPE2=Y 
----------------------------------------------------------------------------
W1          2015-10-10   loc1          2015-10-04          2015-10-07
W2          2015-10-11   loc1          2015-10-03          2015-10-08

I need a similar resultset with only one row for each unique value in ASSETNUM.

Any help would be appreciated!

Justin Cave

Analytic functions to the rescue.

http://sqlfiddle.com/#!4/8ee297/4

select assetnum,
       wodate,
       wonum,
       location,
       last_type1_wodate,
       last_type2_wodate
 from(select assetnum,
             wodate,
             wonum,
             location,
             rank() over (partition by assetnum order by wodate desc) rnk_wodate,
             max(case when type1 = 'Y' then wodate else null end) 
               over (partition by assetnum) last_type1_wodate,
             max(case when type2 = 'Y' then wodate else null end)
               over (partition by assetnum) last_type2_wodate
        from t)
   where rnk_wodate = 1

Walking through what that's doing

rank() over (partition by assetnum order by wodate desc) takes all the rows for a particular assetnum and sorts them by wodate. The predicate on the outside where rnk_wodate = 1 returns just the most recent row. If there can be ties, you may want to use dense_rank or row_number in place of rank depending on how you want ties to be handled.
max(case when type1 = 'Y' then wodate else null end) over (partition by assetnum) takes all the rows for a particular assetnum and finds the value that maximizes the case expression. That will be the last row where type1 = 'Y' for that assetnum.