CODEWITHSUNDEEP
sqloracle-databaseconcatenation
Bigyellowbee
Bigyellowbee

Reputation: 133

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,

Upvotes: 1

Views: 1402

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

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)

Upvotes: 2

Related Questions