I have a table that stores user info such as:

I need to write a query that returns the results in the following format:

I tried doing a LEFT JOIN for each status but that didn't work, any thoughts on how to get the expected results?

CodePudding user response：

if number of statuses is fixed you can do this

Select 
    id user_id,
    open_status,
    open_status_date,
    inprogress_status,
    inprogress_status_date,
    complete_status,
    complete_status_date
from
    (select user_id id from yourTable group by user_id) U left join  
    (select user_id id, status open_status, status_date open_status_date 
     from yourTable where status = 'Open') O on U.id = O.id left join  
    (select user_id id, status inprogress_status, status_date inprogress_status_date 
     from yourTable where status = 'InProgress') P on U.id = P.id left join
    (select user_id id, status complete_status, status_date complete_status_date 
     from yourTable where status = 'Complete') C on U.id = C.id 
Order by id

Break into inline views and join. But this may be not the most efficient way.

ALSO NOTE: if each user definitely has at least "Open" status, you can skip first U inline view and start with O

CodePudding user response：

You want to use a pivot, like this:

    select * from test
    PIVOT(
        max(status_date)
        FOR status
        IN ( 
            'Open',
            'In Progress',
            'Complete'
        )
    )
order by user_id