Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

To display multiple row data in one row

User910243567Jun 11 2019 — edited Jun 11 2019

Hello All,

I am trying to consolidate and display multiple row data into one. Can you please suggest.

WITH t1 AS
  (SELECT 123 SEQ_ID, 'DETROIT' City, to_date('01-JUN-19 00:00:00','dd-mon-rr HH24:MI:SS') start_date, to_date('30-NOV-19 00:00:00','dd-mon-rr HH24:MI:SS') end_date FROM DUAL
  UNION ALL
  SELECT 123 SEQ_ID, 'Toledo' City, to_date('01-JUL-19 00:00:00','dd-mon-rr HH24:MI:SS') start_date, to_date('30-SEP-19 00:00:00','dd-mon-rr HH24:MI:SS') end_date FROM DUAL
  )
SELECT * FROM t1;
Expected output in single row
-----------------------------
SEQ     CITY1     START_DATE1           END_DATE1          CITY2     START_DATE2           END_DATE2
---------------------------------------------------------------------------------------------------
123    DETROIT    01-JUN-19 00:00:00    30-NOV-19 00:00:00 Toledo    01-JUL-19 00:00:00    30-SEP-19 00:00:00

Thanks for your time.

Comments

247514
Frankly, that's question to Oracle development team

SQL*Plus has been around for a long time. There's no major overhaul to this application during all these years and I didn't think Oracle plan to do that in future.

A lot of features you mentioned are already available in sqlplus
If Mysql client can satisfy you so should sqlplus.
There are many setting you can use to achieve the output format you like.

For example,

COL <column_name> FORMAT a<integer>
SET LINESIZE,
PAGESIZE,
PAUSE,
etc, etc
1 - 1

Post Details

Added on Jun 11 2019
#pivot
3 comments
1,581 views