How to concatenate two rows into one in Oracle?

Question

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,

Are you just interested in wk=1 and wk=2, or do you want to generalize this to wk=n? If it's just wk=1 and wk=2, simply left join the table to itself twice filtering by wk=1 and wk=2, respectively, and group by. If it's wk=n, what you want to produce is called a "pivot table" or "crosstab report". Google that. — Asaph
– Asaph, Commented Sep 2, 2016 at 15:59

Sergey Kalinichenko · Accepted Answer · 2016-09-02 16:27:07Z

2

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)

edited Sep 2, 2016 at 16:27

answered Sep 2, 2016 at 15:59

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bigyellowbee Over a year ago

Thank you for your quick solution. I was stuck in looking a way to replace null in id and category fields when records only present in wk=1 or wk=2. Thank you.

Sergey Kalinichenko Over a year ago

@Bigyellowbee You are welcome. I tried the query, and the one I had originally produced more rows than you needed. I added a where condition at the end to eliminate that problem.

Bigyellowbee Over a year ago

Appreciate your attitude.

Collectives™ on Stack Overflow

How to concatenate two rows into one in Oracle?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related