Bigyellowbee Bigyellowbee - 3 months ago 6
SQL Question

How to concatenate two rows into one in Oracle?

I have a table which has such kind of records

ID wk category mo tu wedn
1 1 3 4 4 4
1 1 4 5 5 5
1 1 18 7 7 7
1 2 3 4 4 4
1 2 4 5 5 5
1 2 17 2 2 2


How do I concatenate rows have same category and keep the non-matched rows from either wk=1 and wk=2 as well into something like the following
and data from wk=1 are required in from of data from wk=2:

ID category mo tu wedn mo2 tu2 wedn2
1 3 4 4 4 4 4 4
1 4 5 5 5 5 5 5
1 18 7 7 7 0 0 0
1 17 0 0 0 2 2 2


Thanks,

Answer

Run a full outer join, and use wk to distinguish which rows goes to what side - left or right:

select
    NVL(a.id, b.id)
,   NVL(a.category, b.category)
,   NVL(a.mo, 0)
,   NVL(a.tu, 0)
,   NVL(a.wedn, 0)
,   NVL(b.mo, 0) as mo2
,   NVL(b.tu, 0) as tu2
,   NVL(b.wedn, 0) as wedn2
from MyTable a
full outer join MyTable b ON a.id=b.id AND a.wk=1 AND b.wk=2 AND a.category=b.category
where (a.wk is null or a.wk <> 2) and (b.wk is null OR b.wk <> 1)