Tabularize Oracle Results

  • Hi All,

    I haven't posted here in years so that means I'm a bit rusted. This time around I have an Oracle question. I have the following data that I need to set in tabular format where I take one column and turn it into 8. For instance, my results return 808 row of data that need to convert to 8 columns of 101 rows with a header and footer

    Select (decode(lag(a.serialno)over(order by a.serialno),
            serialno,null,a.serialno)
            ) serialno, d.column, d.number
    from unit a
    join data b on a.tid = b.tid
    join header c on b.testid = c.testid
    join points d on c.headerid = d.headerid
    where a.serialno in ('112233000');

    Results should be like the attached. Thanks for any help.

  • kabaari - Thursday, November 16, 2017 2:08 PM

    Hi All,

    I haven't posted here in years so that means I'm a bit rusted. This time around I have an Oracle question. I have the following data that I need to set in tabular format where I take one column and turn it into 8. For instance, my results return 808 row of data that need to convert to 8 columns of 101 rows with a header and footer

    Select (decode(lag(a.serialno)over(order by a.serialno),
            serialno,null,a.serialno)
            ) serialno, d.valuex, d.pointnumber
    from mfgunit a
    join pumpdata b on a.mfgunitid = b.mfgunitid
    join pumpdataheader c on b.pumptestid = c.pumptestid
    join pumpdatapoints d on c.pumpdataheaderid = d.pumpdataheaderid
    where a.serialno in ('P3170192180');

    Results should be like the attached. Thanks for any help.

    See it's been so long you forgot to post the DDL and some sample data 🙂
    You can find more info on that in this link:
    Forum Etiquette: How to post data/code on a forum to get the best help
    If  you could please post those.

    And a shot in the dark - are you trying to pivot the data? Check the documentation on Pivot and see if it applies to what you are trying to do. Doesn't really look like it from your query but thought I'd throw that out there for you to check.

    Sue

  • Sue,

    You're correct on both fronts - posting DDL and not using Pivot. I'm working on getting the DDL together. Below is another query I wrote only works for one parameter at a time and null is placed for every subsequent 100 rows.

    SELECT a.serialno, e.name,
    case when rownum between 1 and 101  then column end as "PT",
    case when rownum between 102 and 203 then column end as "PP",
    case when rownum between  204 and 305 then column end as "PS",
    case when rownum between  306 and 407 then column end as "PF,
    case when rownum between  408 and 509 then column end as "FT",
    case when rownum between  510 and 611 then  column end as "FP",
    case when rownum between  612 and 713 then column end as "FS",
    case when rownum between  714 and 815 then column end as "FF",
    b.cycletime
    from unit a
    join data b on a.tid = b.tid
    join config e on a.id = e.id
    join header c on b.testid = c.testid
    join points d on c.headerid = d.headerid
    where a.serialno in ('1112222333');

  • Deleted.

  • And the DDL and data has nothing like any of the queries you attempted - columns and tables aren't the same. Just setting it up won't work in SQL Server and interestingly enough, it won't work in Oracle either. I did some changes to get the table created and insert the data but your two queries together appear to be a sequence of things. And I won't download spreadsheets so not much more I can do on my end.

    Sue

  • Sue,

    I had to pull the data into Excel via Oracle to get the desired output. I did however, attempt to aggregate the data into a view thinking that would help but referring back Excel, I was able to make it happen. Thanks for the help.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Its always better to give some sample data for us to work with. Based on your input I have created my own sample data something like this

    visit homepage

    SQL> create table chk_sequences
      2  (
      3  sequence_owner  varchar2(30),
      4  sequence_name   varchar2(30),
      5  min_value       number,
      6  max_value       number,
      7  increment_by    number,
      8  cycle_flag      varchar2(1),
      9  order_flag      varchar2(1),
    10  cache_size      number,
    11  last_number     number,
    12  time_of_exec    date
    13  );

    Table created.

    SQL> insert into chk_sequences
      2  select 'KARTHICK', 'TEST', 1, 100, 1, 'N', 'Y', 10, level, sysdate + level
      3    from dual
      4  connect
      5       by level <= 5;

    5 rows created.

    SQL> select * from chk_sequences;

    SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER TIME_OF_E

    ------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- ----------- ---------

    KARTHICK                       TEST                                    1        100            1 N Y      10      1 09-SEP-15

    KARTHICK                       TEST                                    1        100            1 N Y      10      2 10-SEP-15

    KARTHICK                       TEST                                    1        100            1 N Y      10      3 11-SEP-15

    KARTHICK                       TEST                                    1        100            1 N Y      10      4 12-SEP-15

    KARTHICK                       TEST                                    1        100            1 N Y      10      5 13-SEP-15

     

    Basically you want to PIVOT your table for each sequence. As I can see you are in 11g you can use PIVOT function instead of performing self join multiple times.

     

    Here is an example.

     

    SQL> with t

    SQL> with t
      2  as
      3  (
      4  select sequence_name
      5       , cache_size
      6       , time_of_exec
      7       , last_number
      8       , row_number() over(partition by sequence_owner, sequence_name order by time_of_exec) rno
      9    from chk_sequences s
    10  )
    11  select sequence_name
    12       , cache_size
    13       , a_last_number
    14       , b_last_number
    15       , c_last_number
    16       , d_last_number
    17       , e_last_number
    18       , a_toe
    19       , b_toe
    20       , c_toe
    21       , d_toe
    22       , e_toe
    23    from t
    24    pivot (
    25               max(time_of_exec) as toe
    26             , max(last_number)  as last_number
    27           for rno in (1 as a, 2 as b, 3 as c, 4 as d, 5 as e)
    28          );

    SEQUENCE_NAME                  CACHE_SIZE A_LAST_NUMBER B_LAST_NUMBER C_LAST_NUMBER D_LAST_NUMBER E_LAST_NUMBER A_TOE     B_TOE     C_TOE     D_TOE     E_TOE

    ------------------------------ ---------- ------------- ------------- ------------- ------------- ------------- --------- --------- --------- --------- ---------

    TEST                                   10             1             2             3             4          5    09-SEP-15 10-SEP-15 11-SEP-15 12-SEP-15 13-SEP-15

    SQL>

     

    But it does not end there, does it? You want the column projection to go dynamic i.e. if there is a 6th sequence entry in the table you want one more column added to LAST_NUMBER an

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply