1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pl/sql : build select statement to return data in 1 row

Discussion in 'SQL PL/SQL' started by akika, Jun 16, 2017.

Tags:
  1. akika

    akika Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Hi,

    I need to build a select from below table & data to output
    a single row for the ID:
    Data in table test_tbl.. attached snapshot

    The output of select shall be as follows, in single row per ID
    e.g 100 21:00 21:30 22:00
    200 15:00 15:30 16:00 16:00 17:00
     

    Attached Files:

    Last edited: Jun 16, 2017
  2. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    28
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    Hi,

    Try this

    with x as(
    select 100 id,'21:00' data_1,null data_2,null data_3,null data_4,null data_5 from dual
    union all
    select 100,null,'21:30',null,null,null from dual
    union all
    select 100,null,null,'22:00',null,null from dual
    union all
    select 200,'15:00',null,null,null,null from dual
    union all
    select 200,null,'15:30',null,null,null from dual
    union all
    select 200,null,null,'16:00',null,null from dual
    union all
    select 200,null,null,null,'16:30',null from dual
    union all
    select 200,null,null,null,null,'17:00' from dual)
    select id, max(data_1) data_1, max(data_2) data_2, max(data_3) data_3, max(data_4) data_4, max(data_5) data_2
    from x
    group by id;

    Can be max, min, avg, ...


    In oracle 11 ( I did not test)

    with x as(
    select 100 id,'21:00' data_1,null data_2,null data_3,null data_4,null data_5 from dual
    union all
    select 100,null,'21:30',null,null,null from dual
    union all
    select 100,null,null,'22:00',null,null from dual
    union all
    select 200,'15:00',null,null,null,null from dual
    union all
    select 200,null,'15:30',null,null,null from dual
    union all
    select 200,null,null,'16:00',null,null from dual
    union all
    select 200,null,null,null,'16:30',null from dual
    union all
    select 200,null,null,null,null,'17:00' from dual)
    select id,
    substr(listagg(data_1) WITHIN GROUP (ORDER BY data_1),1,10) data_1,
    substr(listagg(data_2) WITHIN GROUP (ORDER BY data_2),1,10) data_2,
    substr(listagg(data_3) WITHIN GROUP (ORDER BY data_3),1,10) data_3,
    substr(listagg(data_4) WITHIN GROUP (ORDER BY data_4),1,10) data_4,
    substr(listagg(data_5) WITHIN GROUP (ORDER BY data_5),1,10) data_5
    from x
    group by id;
     
  3. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    28
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    ops, do not use AVG...
     
  4. akika

    akika Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    great it does meet return in single row the data per id....
    btw instead of hardcoding the ID and value .. is it possible to amend the select for dynamic fields?
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    702
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Can you specifically tell : "what do you need" ?

    For a start :1) provide here the scripts of creating table and script for filling of data
    2) you can to use the table-function,parameterized of the views
    simple example :
    Code (Text):

    with your_table(id,date_1,date_2,date_3) as
      (
        select
          level id,
          trunc(sysdate) + LEVEL/24,
          trunc(sysdate) + LEVEL/24+INTERVAL '20' MINUTE,
          trunc(sysdate) + LEVEL/24+INTERVAL '40' MINUTE
        from dual
        connect by level <=7
      )
    select
      id,to_char(date_1,'hh24:mi')date_1,to_char(date_2,'hh24:mi') date_2,to_char(date_3,'hh24:mi') date_3
    from
      your_table
    where id = &your_id;
     
     
    Last edited: Jun 16, 2017
  6. akika

    akika Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    hi,
    scripts attached for tbl creation & insert.

    The table has different ID,
    for each id, the data will be different rows for data1 , data 2 , data 3, data 4 and data 5
    I need a select that will return the ID , data1 , data 2 , data 3, data 4 and data 5 in a single row

    the data 1 to 5 are datetime log.. start time data1 to end time data5
     

    Attached Files:

    Last edited: Jun 16, 2017
  7. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    28
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    I do not know if I understood correctly, English is not my native language.

    but

    select id, max(data_1) data_1, max(data_2) data_2, max(data_3) data_3, max(data_4) data_4, max(data_5) data_5
    from test_tbl
    group by id;

    is this?
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    702
    Likes Received:
    140
    Trophy Points:
    830
    Location:
    Russian Federation
    A few questions :1) why ID is a text field and 2) why in the fields of data* stored the string rather than use pseudo-date plus a time component

    If you need get fields data_* by ID , then you need to use aggregate functions : min/max(.If you want to get (as is) the row by ID , then see the example I provided earlier.
     
    Last edited: Jun 16, 2017