SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tabularize Oracle Results


Tabularize Oracle Results

Author
Message
kabaari
kabaari
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1819 Visits: 411
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
Sue_H
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69628 Visits: 14486
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 Smile
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
kabaari
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1819 Visits: 411
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
kabaari
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1819 Visits: 411
Deleted.






Sue_H
Sue_H
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69628 Visits: 14486


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
kabaari
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1819 Visits: 411
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search