Need to add a unique row count for every repeating variable

by Nimesh Maheshwari
(Daytona Beach, FL, USA)

It will be easier if I demonstrate what I am talking about:


I have a table with the following layout:

Emplid term Major
123 1992 abc
123 1992 def
123 1993 abc
456 1992 abc


I want to add another column which would act as a counter based on EMPLID and term, so the outcome will look like

Emplid term Major counter
123 1992 abc 1
123 1992 def 2
123 1993 abc 1
456 1992 abc 1

Can this be done ?

The reason I am doing this is to transposed row into column using decode. As the number of majors can be huge i thought of adding a counter to keep track of number of majors each student has and then use decode to transpose, so the final outcome will look like

Emplid term Major1 Major2
123 1992 abc def
123 1993 abc
456 1992 abc

Thanks for all your help,

Nimesh

Comments for Need to add a unique row count for every repeating variable

Average Rating starstarstarstarstar

Click here to add your own comments

Mar 20, 2015
Rating
starstarstarstarstar
Use Oracle PL/SQL collections to do this
by: Anonymous

One way of doing this would be to use PL/SQL. You could fetch the data ordered by emplid and term (and major if you need them in a particular order) into a PL/SQL collection of records (of type cursor%rowtype) and then update the collection in situ to label each major as 1,2,3 etc.

To improve performance you should use BULK COLLECT when fetching the data and add a LIMIT for each fetch to avoid using too much memory in one go (so you would obviously have to do this in a loop). Then, once you've labeled all the makors for each student in each iteration through the data you write it to a temporary table or the orignal table using the FORALL PL/SQL command which updates or inserts the rows in one go rather than one at a time.

Assuming your input table is called emp and your output table is new_emp your PL/SQL code would look something like the following:


DECLARE
  CURSOR emp_cur IS
     SELECT emplid, term, major, 0 label
     FROM emp
     ORDER BY emplid, term;

  TYPE emp_tab_ty IS TABLE OF

  emp_cur%ROWTYPE;

  emp_tab emp_tab_ty; -- declare the collection

  prev_emplid emp.emplid%TYPE:=0;
  prev_term emp.term%TYPE:=0;

  counter PLS_INTEGER;
  i PLS_INTEGER;

BEGIN

  OPEN emp_cur;

  LOOP

     FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT 100;

     EXIT WHEN emp_tab.COUNT=0;

     FOR i IN 1..emp_tab.COUNT LOOP

        IF (prev_emplid <> emp_tab(i).emplid) OR
        (prev_term <> emp_tab(i).term) THEN
           counter := 0;
           prev_emplid := emp_tab(i).emplid;
           prev_term := emp_tab(i).term;
        END IF;

        counter := counter + 1;
        emp_tab(i).label := counter;

     END LOOP;

     FORALL j IN emp_tab.FIRST..emp_tab.LAST
       INSERT INTO new_emp2 VALUES
       (emp_tab(j).emplid, emp_tab(j).term, emp_tab(j).major, emp_tab(j).label);

  END LOOP;

  CLOSE emp_cur;

END;

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.