Is it possible to create a running counter

[fmrock]

I have a table that contians charges per account.

There can be up to say 6 charges max per account.

Here is a simple query to pull back one account.

Select Account_Num,CPT From Charges
WHERE Account_Num=1

and the results would look like

Account_Num CTP
1 11111
1 22222
1 33333
1 44444

Is there a way to put a number by each charge, and then reset per account.
Account_Num CTP Number
1 11111 1
1 22222 2
1 33333 3
1 44444 4

My end goal is to get all the charges in one line so the header would be like

Account_Num, CPT1,CPT2,CTP3,CPT4

I figured if i could get the counter next to each charge i could use a group by and a set of cases put this into one line.

Any ideas would be great.

Thanks

 

[taupirho]

You can use analytics for this e.g

Select Account_Num,CPT,
row_number() over (partition by account_num order by account_num) rn
From Charges
WHERE Account_Num=1



In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

...And to obtain the final, strung-together results for which you were hoping, FM, there are multiple methods to achieve them:

Table Data:

select * from charges order by account_num;

ACCOUNT_NUM        CPT
----------- ----------
          1      11111
          1      22222
          1      33333
          1      44444
          3      12345
          5      13579
          5      24680
          7      99999
          7      88888
          7      77777
          7      66666
          7      55555
          7      44444

Method 1: Variation on Taupirho's excellent suggestion:

col results format a40
select a.anum||','||a1.cpt
       ||decode(a2.cpt,null,null,','||a2.cpt)
       ||decode(a3.cpt,null,null,','||a3.cpt)
       ||decode(a4.cpt,null,null,','||a4.cpt)
       ||decode(a5.cpt,null,null,','||a5.cpt)
       ||decode(a6.cpt,null,null,','||a6.cpt) results
  from (select distinct account_num anum from charges) a
      ,(Select Account_Num,CPT, row_number() over (partition by account_num order by account_num) rn
          From Charges) a1
      ,(Select Account_Num,CPT, row_number() over (partition by account_num order by account_num) rn
          From Charges) a2
      ,(Select Account_Num,CPT, row_number() over (partition by account_num order by account_num) rn
          From Charges) a3
      ,(Select Account_Num,CPT, row_number() over (partition by account_num order by account_num) rn
          From Charges) a4
      ,(Select Account_Num,CPT, row_number() over (partition by account_num order by account_num) rn
          From Charges) a5
      ,(Select Account_Num,CPT, row_number() over (partition by account_num order by account_num) rn
          From Charges) a6
 where (a.anum = a1.account_num(+) and a1.rn(+) = 1)
   and (a.anum = a2.account_num(+) and a2.rn(+) = 2)
   and (a.anum = a3.account_num(+) and a3.rn(+) = 3)
   and (a.anum = a4.account_num(+) and a4.rn(+) = 4)
   and (a.anum = a5.account_num(+) and a5.rn(+) = 5)
   and (a.anum = a6.account_num(+) and a6.rn(+) = 6)
/

RESULTS
----------------------------------------
1,11111,22222,33333,44444
3,12345
5,13579,24680
7,99999,88888,77777,66666,55555,44444

4 rows selected.

Method 2: User-defined function combined with simple SELECT:

create or replace function get_cpts (anum_in number) return varchar2 is
        hold_str varchar2(200);
    begin
        for x in (Select Account_Num,CPT
                    From Charges
                   where account_num = anum_in
                   order by cpt) loop
            hold_str := hold_str||','||x.cpt;
        end loop;
        return ltrim(hold_str,',');
    end;
/

Function created.

select distinct account_num||','||get_cpts(account_num) results
  from charges
 order by results
/
RESULTS
----------------------------------------
1,11111,22222,33333,44444
3,12345
5,13579,24680
7,44444,55555,66666,77777,88888,99999

4 rows selected.

Let us know if thes suggestions resolve your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”