Home » SQL & PL/SQL » SQL & PL/SQL » How to flatten one column from multiple rows (Oracle 11.2.9.1.0)
How to flatten one column from multiple rows [message #673939] Tue, 18 December 2018 11:06 Go to next message
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.
How to flatten one column from multiple rows [message #673940 is a reply to message #673939] Tue, 18 December 2018 11:07 Go to previous messageGo to next message
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 #673941 is a reply to message #673940] Tue, 18 December 2018 11:10 Go to previous messageGo to next message
gunderj
Messages: 17
Registered: April 2016
Location: California
Junior Member
Note the desired result above was meant to show two columns - subject and body with body being the only column flattened. Thanks.
Re: How to flatten one column from multiple rows [message #673942 is a reply to message #673941] Tue, 18 December 2018 11:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
VARCHAR Data Type → Do not use the VARCHAR data type. Use the VARCHAR2 data type instead.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/LISTAGG.html#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466

>(Oracle 11.2.9.1.0)?

post full results from SQL below

SELECT * FROM V$VERSION;
Re: How to flatten one column from multiple rows [message #673943 is a reply to message #673942] Tue, 18 December 2018 11:26 Go to previous messageGo to next message
gunderj
Messages: 17
Registered: April 2016
Location: California
Junior Member
Thanks, this worked perfectly.
Re: How to flatten one column from multiple rows [message #673944 is a reply to message #673941] Tue, 18 December 2018 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

gunderj wrote on Tue, 18 December 2018 18:10
Note the desired result above was meant to show two columns - subject and body with body being the only column flattened. Thanks.
From one of your previous topics:

BlackSwan wrote on Mon, 25 April 2016 16:58
...
How to use {code} tags and make your code easier to read

This is the same thing to post result chart, and don't forget to align the columns.

Re: How to flatten one column from multiple rows [message #673952 is a reply to message #673944] Wed, 19 December 2018 08:14 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
Since the email id is the grouping key, but there is no ordering column, how will you determine the order in which the rows are "flattened" for a given key?

JP
Re: How to flatten one column from multiple rows [message #674029 is a reply to message #673952] Thu, 27 December 2018 08:33 Go to previous message
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;
Previous Topic: NVL not working in Cursor Subquery
Next Topic: Transactions than can be rejected, temporary tables or not?
Goto Forum:
  


Current Time: Thu Mar 28 14:21:58 CDT 2024