Display multiple records in one 1 row with multiple columns

Question

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?

[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) Please in code questions give a [mre]. [ask] [Help] Show what you are able to do. Research before considering asking & reflect research in a question. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) — philipxy, Jul 19 '22 at 03:02
Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Jul 19 '22 at 04:39

score 2 · Accepted Answer · answered Jul 19 '22 at 00:22

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

score 0 · Answer 2 · answered Jul 19 '22 at 00:26

0

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

answered Jul 19 '22 at 00:26

OldProgrammer

12,050
4
24
45

score -1 · Answer 3 · answered Jul 19 '22 at 01:55

Suppose every user has a "Open" status

With
 open as (select * from table where status = 'Open'),
 inp as (select * from table where status = 'In Progress'),
 comp as (select * from table where status = 'Complete')
select o.user_id,o.status open_status, o.status_date open_status_date,i.status InProgress_status, i.status_date InProgress_status_date,c.status complete_status, c.status_date complete_status_date
from open o, inp i, comp c
where o.user_id=i.user_id(+)
and o.user_id=c.user_id(+)

Display multiple records in one 1 row with multiple columns

3 Answers3