How to flatten one column from multiple rows [message #673939] |
Tue, 18 December 2018 11:06 |
|
gunderj
Messages: 17 Registered: April 2016 Location: California
|
Junior Member |
|
|
Looking for a way to "flatten" one column from multiple rows with this simple example...
create table email_hdr(
email_id varchar2(3),
subject varchar(50)
);
create table email_line(
email_id varchar2(3),
line varchar(80)
);
insert into email_hdr values ('1','mySubject');
insert into email_line values ('1','this is line one');
insert into email_line values ('1','this is line two');
insert into email_line values ('1','this is line three');
How to write a query that will flatten the email_line.line to be this:
subject body
================ ==========================================================
mySubject this is line one this is line two this is line three
Thanks for any advice.
|
|
|
|
|
|
|
|
|
Re: How to flatten one column from multiple rows [message #674029 is a reply to message #673952] |
Thu, 27 December 2018 08:33 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I agree with JP. Oracle has no order in a select unless you specify an order by clause. A better design using your setup would be
create table email_hdr(
email_id varchar2(3),
subject varchar2(50)
);
create table email_line(
email_id varchar2(3),
seq# number,
line varchar2(80)
);
insert into email_hdr values ('1','mySubject');
insert into email_line values ('1',1,'this is line one');
insert into email_line values ('1',2,'this is line two');
insert into email_line values ('1',3,'this is line three');
SELECT email_id,LISTAGG(line, ' ') WITHIN GROUP (ORDER BY seq#) body
FROM email_line
group by email_id;
|
|
|