November 16, 2017 at 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.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.
November 16, 2017 at 4:39 pm
kabaari - Thursday, November 16, 2017 2:08 PMHi 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
November 17, 2017 at 11:51 am
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');
November 19, 2017 at 7:55 am
Deleted.
November 20, 2017 at 5:00 pm
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
November 21, 2017 at 11:13 am
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.
January 1, 2020 at 2:00 pm
This was removed by the editor as SPAM
January 19, 2020 at 3:07 pm
This was removed by the editor as SPAM
June 4, 2020 at 11:02 am
This was removed by the editor as SPAM
November 2, 2020 at 1:47 pm
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
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