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

Question

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!

Answer 1

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.

Answer 2

I think the conceptually easiest approach to this is to simply view your problem as 3 separate queries, each of which is doing a GROUP BY to obtain some specific thing (the latest WODATE, the latest WODATE for Type1, and the latest WODATE for Type2). These queries can easily be joined together to give you the output you want.

SELECT T.ASSETNUM, t1.LATEST_WODATE, T.LOCATION, t2.LATEST_WODATE_TYPE1,
    t3.LATEST_WODATE_TYPE2
FROM T INNER JOIN
(
    SELECT ASSETNUM, MAX(WODATE) AS LATEST_WODATE
    FROM T
    GROUP BY ASSETNUM
) t1
ON T.ASSETNUM = t1.ASSETNUM AND T.WODATE = t1.LATEST_WODATE
INNER JOIN
(
    SELECT ASSETNUM, MAX(WODATE) AS LATEST_WODATE_TYPE1
    FROM T
    WHERE TYPE1 = 'Y'
    GROUP BY ASSETNUM
) t2
ON T.ASSETNUM = t2.ASSETNUM
INNER JOIN
(
    SELECT ASSETNUM, MAX(WODATE) AS LATEST_WODATE_TYPE2
    FROM T
    WHERE TYPE2 = 'Y'
    GROUP BY ASSETNUM
) t3
ON T.ASSETNUM = t3.ASSETNUM

Click the link below for a running demo:

SQLFiddle