Tabularize Oracle Results

  • kabaari

    SSCrazy

    Points: 2175

    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.

  • Sue_H

    SSC Guru

    Points: 89721

    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

  • kabaari

    SSCrazy

    Points: 2175

    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');

  • kabaari

    SSCrazy

    Points: 2175

    Deleted.

  • Sue_H

    SSC Guru

    Points: 89721

    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

  • kabaari

    SSCrazy

    Points: 2175

    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.

Viewing 6 posts - 1 through 6 (of 6 total)

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