Grab the first rows and put it to column wise

Advertisement
Below are two dump, i want to grab the first 2 rows in ascending order by from_date and put to column wise, the first row label first_charge and second row as second_charge. AND throw away rows from 3 are not needed.
1st dump 5 columns are my input,
2nd dump 3 columnS ARE my desired output.
I have tried the rank function but can someone please confirm this is correct way to do this for all the accounts in the table
Script (table and data) for 1st dump is at bottom of post.
Can anyone help resolve this issue - thanks.
--FIRST DUMP
ACCOUNT_ID     CHARGE_AMT     CHARGE_DATE     FROM_DATE     THRU_DATE
212855740     14.52          5/04/2012     14/03/2012     31/03/2012
212855740     25          5/04/2012     1/04/2012     30/04/2012
212855740     25          5/05/2012     1/05/2012     31/05/2012
212855740     25          5/06/2012     1/06/2012     30/06/2012
212855740     25          5/07/2012     1/07/2012     31/07/2012
212855740     25          5/08/2012     1/08/2012     31/08/2012
212855740     25          5/09/2012     1/09/2012     30/09/2012
212855740     25          5/10/2012     1/10/2012     31/10/2012
212855740     25          5/11/2012     1/11/2012     30/11/2012
--DESIRED DUMP                    
ACCOUNT_ID     PRO_CHARGE     MONTHLY_CHARGE          
212855740     14.52          25          
WITH CHARGE_TABLE_QUERY
     AS (SELECT account_id,
                charge_amt,
                charge_date,
                from_date,
                thru_date,
                ROW_NUMBER ()
                   OVER (PARTITION BY account_id ORDER BY from_Date)
                   row_num
           FROM my_tbl)
  SELECT account_id,
         MAX (CASE WHEN ROW_NUM = 1 THEN CHARGE_AMT ELSE 0 END) PRO_CHARGE,
         MAX (CASE WHEN ROW_NUM = 2 THEN CHARGE_AMT ELSE 0 END) MONTHLY_CHARGE
    FROM CHARGE_TABLE_QUERY q
   WHERE row_num IN (1, 2)
GROUP BY account_id;
Advertisement

Replay

ricard888 wrote:
what happens if a new account where there is no second charge yet. can i have either null or 0 in the monthly_charge.You could test it easily. Anyhow, it will work without any change in the code
insert into my_table
select 1,50,CHARGE_DATE,FROM_DATE,THRU_DATE
from my_table
where rownum = 1;
1 rows inserted.
with CHARGE_TABLE_QUERY as
  select account_id,charge_amt,
         ROW_NUMBER ()
            OVER (PARTITION BY account_id ORDER BY from_Date)  rn,
         lead(charge_amt)
           OVER (PARTITION BY account_id ORDER BY from_Date) MONTHLY_CHARGE
  from my_table
select account_id,charge_amt pro_charge,monthly_charge
from CHARGE_TABLE_QUERY
where rn = 1;
ACCOUNT_ID PRO_CHARGE MONTHLY_CHARGE
         1         50               
212855740      14.52             25

Can you move a row in a DataGridView to the first row and move the other rows down to make room?

I'm using a DataGridView (I call it "dg") to display a list of items. I want the user to be able to position the cursor on a row in the middle of the list then click a button to move that row to the top of the list (and move the other rows betwe

Infopath submit button only works for the first row in a repeating table‏

Hi, I have a InfoPath Email submit button issue I could not figure out. I have a master/detail repeating table in my form, and one of fields is "EmailAddress" which shows customer's email address. I created a submit button and put the "Emai

Af:tableselectmany always selects first row and ONLY first row

My table in jsp: <af:table value="#{bindings.AluNoExpeAluM1.collectionModel}" var="row" rows="#{bindings.AluNoExpeAluM1.rangeSize}" first="#{bindings.AluNoExpeAluM1.rangeStart}" emptyText="#{bindings.AluNoEx

Unable to refresh page when insert the first row in viewobject

I am using JDeveloper 10g to develop a JSP page, DataAction, AppModule and ViewObject. But when I am trying to use the following code to insert the first row, the page is not getting refresh. The quite weird thing is when I insert the second row, it

How to clear a cursor list without clearing the first row??

Hi all, good to see ya ... I'm having this problem , that i'm using the first row ín a cursor list as a cursor in another graph. The rest of the rows are used as test sets i can add. So  when finnish with the tests if some was set, i have to delete o

BUG - First create actually replaces the first row of the table

I would like to be able to add new rows to a table and bound the create function to the button. The issue I encounter is the first time I press the button it instead actually changes the first row to be the newly inserted row instead of adding a new

Pie Chart Only Displays the Data of the First Row of the Table

Hi Experts, I have a problem that the pie chart will not change when click on a second row or other rows on the table. It only displays the data of the first row of the table. How can I set up to make it reflect on any rows when I click the table? Pl

DetailStamp facet iteration stops on the first row.

Using Jdeveloper 10.1.3.2 using <f: facet name="detailStamp" I am having difficulty getting the iteration working correctly. I have placed a related child table to the parent table in the detailStamp. When I run the page I get a Master-Detail

To get the first row

hi all, i have query like select container,size,serial from containers order by serialin the above query for the same container and size there might be more than one serial.out of that i have to sort it by serial and fetch the first row. can we fetch

What is the difference between the sports edition apple watch and the normal edition, is it the bands different or do the they have different apps. Also could you take the bands off and put new ones on(change the bands)?

what is the difference between the sports edition apple watch and the normal edition, is it the bands different or do the they have different apps. Also could you take the bands off and put new ones on(change the bands)?what is the difference between

Anyone out there that can maybe help. I was using my 5s to record my meeting minutes for the first time and clicked cancel cos I wanted to continue recording and instead deleted it all! :( anyway to get it back?

Anyone out there that can maybe help. I was using my 5s to record my meeting minutes for the first time and clicked cancel cos I wanted to continue recording and instead deleted it all! :( anyway to get it back?The other Allan is being facetious--you

I got a MacBook Pro for the first time and just install iTunes.... how to I get my purchased songs from my Windows 7 to my new MacBook Pro

I got a MacBook Pro for the first time and just install iTunes.... how to I get my purchased songs from my Windows 7 to my new MacBook Pro..... please try to make it easier for me since im new to the Mac OS1) Copy all music files to a thumb drive / e

HT1657 trying to rent a movie on apple tv for the first time and can't do it without authorizing my computer on itunes store but can't find the menu on the iTunes site that lets me authorize my computer. any thoughts out there?

trying to rent a movie on apple tv for the first time and have to authorize my computer but can't find the itunes menu that allows me to do this.  any thoughts out there? If the computer's running Mac OS X, move the cursor to the very top of the comp

TS3297 I connected my new iphone to my computer for the first time and lost all my contacts.  any way to get them back

I connected my new iphone to my computer for the first time and lost all my contacts.  any way to get them backDid you have them stored in iCloud?Read other 2 answers

My ipad was stolen. I reported it to the Toronto Police and put it as lost mode. Now what?

I reported it to the Toronto Police and put it as lost mode. Now what?If the iPad was running iOS 7, the thief will not ever be able to use it. iCloud: Find My iPhone Activation Lock in iOS 7 http://support.apple.com/kb/HT5818 Apple (and no one else)

I would like to change of type of subscription to improve its level. Should I cancel the first one and then take another one ?

hello I would like to change my type of subscription (adobe reader) to improve to get a higher level. Should I cancel the first one and then take another one ? thks cecileHi Cecile, Please Contact Customer Care; an agent will be able to change your s

Grand total level of the percentage column giving value from the first row of column

Hi Guys, I have a requirement like to show the percentage value which the calculation of two columns , in the grand total level . I think it can be achievaable by giving the aggregation rule for that column as Server complex Aggregate . eg. Fiscal Ye

Buying a Macbook pro for the first time and i need your opinion

Hello everyone! I'm buying a Macbook pro for the first time and i need your opinion. I'm a Web Developer / Graphic Designer, and i need this macbook for my work. Currently i have these 2 options: 1) Macbook Pro with Retina (13") 128GB SSD, 2.5GHz Int

Please help me. I've just lost my iphone so I related to my backup contacts on icloud to get my contacts back. But my contacts only show at the first time and then they dissappeared, my contacts was empty. How can I see my contacts?

Please help me. I've just lost my iphone so I related to my backup contacts on icloud to get my contacts back. But my contacts only show at the first time and then they dissappeared, my contacts was empty. How can I see my contacts?If they aren't on