If you are on Oracle....as Database then
Step1
create a table temp1 will foll structure
SQL> desc temp1
Name Null? Type
------------------------------- -------- ----
USERID VARCHAR2(35)
ACCOUNT1 NUMBER
This Table Is Of Same Structure of your existing table (except the data type .... you could keep your datatype)
Create a new table say temp which will have 8 more cols for account1....account8
create all the unique userid records in temp by following sql statement
insert into temp(userid) select userid from temp1
SQL> desc temp
Name Null? Type
------------------------------- -------- ----
USERID VARCHAR2(35)
ACCOUNT1 NUMBER
ACCOUNT2 NUMBER
ACCOUNT3 NUMBER
ACCOUNT4 NUMBER
ACCOUNT5 NUMBER
ACCOUNT6 NUMBER
ACCOUNT7 NUMBER
ACCOUNT8 NUMBER
Now The PL/SQL
cursor c1 is select userid,account1 from temp1
order by userid;
x varchar(35);
x1 varchar(35):='xx';
y number;
y1 number:=0;
begin
open c1;
Loop
Fetch c1 into x,y;
exit when c1%notfound;
if x != x1
Then
y1:=1 ;
x1:=x;
else
y1:=nvl(y1,0)+1;
End If;
if y1 = 1 then
update temp set account1 = y
where userid = x;
End If;
if y1 = 2 then
update temp set account2 = y
where userid = x;
End If;
if y1 = 3 then
update temp set account3 = y
where userid = x;
End If;
if y1 = 4 then
update temp set account4 = y
where userid = x;
End If;
if y1 = 5 then
update temp set account5 = y
where userid = x;
End If;
if y1 = 6 then
update temp set account6 = y
where userid = x;
End If;
if y1 = 7 then
update temp set account7 = y
where userid = x;
End If;
if y1 = 8 then
update temp set account8 = y
where userid = x;
End If;
End Loop;
Close c1;
End;
This Is A Crude way of Getting what you want...
Professionaly i would have done it by using Pro*C using the prepare statement for dynamic sql so that i would have avoided using 8 update statements.
But Hope Your Purpose Is Solved Coz code is Tested The Out Put Is As Follows
SQL> select * from temp1;
USERID ACCOUNT1
----------------------------------- ---------
suzypen2 1234567
suzypen2 3215789
suzypen2 9274618
lime#w2 1027839
suzypen2 4356543
suzypen2 9745451
suzypen2 7589053
lime#w2 6748906
lime#w2 4874325
lime#w2 8902347
orange 2345678
orange 2233445
orange 7676767
after executing the PL/SQL prog ...The Output of temp table is as desired by you.
SQL> select * from temp;
USERID ACCOUNT1 ACCOUNT2 ACCOUNT3 ACCOUNT4 ACCOUNT5 ACCOUNT6 ACCOUNT7 ACCOUNT8
----------------------------------- --------- --------- --------- --------- --------- --------- --------- ---------
orange 2345678 2233445 7676767
lime#w2 1027839 6748906 4874325 8902347
suzypen2 1234567 3215789 9745451 7589053 4356543 9274618
Few Records were added for testing purpose to your sample data
Step1
create a table temp1 will foll structure
SQL> desc temp1
Name Null? Type
------------------------------- -------- ----
USERID VARCHAR2(35)
ACCOUNT1 NUMBER
This Table Is Of Same Structure of your existing table (except the data type .... you could keep your datatype)
Create a new table say temp which will have 8 more cols for account1....account8
create all the unique userid records in temp by following sql statement
insert into temp(userid) select userid from temp1
SQL> desc temp
Name Null? Type
------------------------------- -------- ----
USERID VARCHAR2(35)
ACCOUNT1 NUMBER
ACCOUNT2 NUMBER
ACCOUNT3 NUMBER
ACCOUNT4 NUMBER
ACCOUNT5 NUMBER
ACCOUNT6 NUMBER
ACCOUNT7 NUMBER
ACCOUNT8 NUMBER
Now The PL/SQL
Code:
declare cursor c1 is select userid,account1 from temp1
order by userid;
x varchar(35);
x1 varchar(35):='xx';
y number;
y1 number:=0;
begin
open c1;
Loop
Fetch c1 into x,y;
exit when c1%notfound;
if x != x1
Then
y1:=1 ;
x1:=x;
else
y1:=nvl(y1,0)+1;
End If;
if y1 = 1 then
update temp set account1 = y
where userid = x;
End If;
if y1 = 2 then
update temp set account2 = y
where userid = x;
End If;
if y1 = 3 then
update temp set account3 = y
where userid = x;
End If;
if y1 = 4 then
update temp set account4 = y
where userid = x;
End If;
if y1 = 5 then
update temp set account5 = y
where userid = x;
End If;
if y1 = 6 then
update temp set account6 = y
where userid = x;
End If;
if y1 = 7 then
update temp set account7 = y
where userid = x;
End If;
if y1 = 8 then
update temp set account8 = y
where userid = x;
End If;
End Loop;
Close c1;
End;
This Is A Crude way of Getting what you want...
Professionaly i would have done it by using Pro*C using the prepare statement for dynamic sql so that i would have avoided using 8 update statements.
But Hope Your Purpose Is Solved Coz code is Tested The Out Put Is As Follows
SQL> select * from temp1;
USERID ACCOUNT1
----------------------------------- ---------
suzypen2 1234567
suzypen2 3215789
suzypen2 9274618
lime#w2 1027839
suzypen2 4356543
suzypen2 9745451
suzypen2 7589053
lime#w2 6748906
lime#w2 4874325
lime#w2 8902347
orange 2345678
orange 2233445
orange 7676767
after executing the PL/SQL prog ...The Output of temp table is as desired by you.
SQL> select * from temp;
USERID ACCOUNT1 ACCOUNT2 ACCOUNT3 ACCOUNT4 ACCOUNT5 ACCOUNT6 ACCOUNT7 ACCOUNT8
----------------------------------- --------- --------- --------- --------- --------- --------- --------- ---------
orange 2345678 2233445 7676767
lime#w2 1027839 6748906 4874325 8902347
suzypen2 1234567 3215789 9745451 7589053 4356543 9274618
Few Records were added for testing purpose to your sample data