To display multiple row data in one row

User910243567

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.

Cookiemonster76

That's a PIVOT query (that's an oracle keyword, look it up).

Are you only ever going to be combining two rows into one or can the number of rows vary (and how variable is it?)

Mustafa_KALAYCI

check this:

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 (select seq_id, city, start_date, end_Date, rownum as rr from t1)pivot (min(start_date) start_date,min(end_date) end_date, min(city) as city for rr in (1,2)); 

but this can not be done in fully dynamic (there are workarounds though) I assumed you have 2 city only.

Frank Kulash

Hi,

Thanks for posting the sample data and desired results.  Don't forget to post an explanation of how you get those results from that data.  There can be many different ways to get the same results from a small sample set  that all get different results from your real table, especially if there are only 2 rows of sample data.  So post an explanation like this:

"I want the output to have 1 row for each distinct seq_id, showing the 1st 2 cities (in alphabetic order), along with their start_ and end_dates.  If there is only 1 row in the table for a seq_id, then the columns for the 2nd city should all be NULL.  If there are 3 or more rows in the table with the same seq_id, then ignore all but the first 2 (in alphabetic order)."

If that is what you want, then you can modify the solution Mustafa posted in reply #2 like this:

WITH    got_rr    AS(    SELECT  seq_id, city, start_date, end_date    ,       ROW_NUMBER () OVER ( PARTITION BY  seq_id                                 ORDER BY      city                               )  AS rr    FROM    t1--  WHERE   ...   -- If you need any filtering, put it here)SELECT    *       -- Or list columns and assign aliasesFROM      got_rrPIVOT     (    MIN (city)        AS city          ,    MIN (start_date)  AS start_date          ,    MIN (end_date)    AS end_date          FOR  rr  IN (1, 2)          )ORDER BY  seq_id;

Output from your sample data:

  SEQ_ID 1_CITY  1_START_DAT 1_END_DATE  2_CITY  2_START_DAT 2_END_DATE-------- ------- ----------- ----------- ------- ----------- -----------     123 DETROIT 01-Jun-2019 30-Nov-2019 Toledo  01-Jul-2019 30-Sep-2019

If you don't like the column names generated, you can assign aliases in the SELECT clause of the main query.