To transpose or not to transpose?

[LeeFarrant]

I have a table which is in the following format
CUSTID SUBID
Customer1 Subscriber1
Customer1 Subscriber2
Customer1 Subscriber3
Customer1 Subscriber4
Customer2 Subscriber5
Customer2 Subscriber6
Customer3 Subscriber7
Customer3 Subscriber8

I need to transform this table into the following format so there is only one record for each customer and the subscriber fields 1-x are going across the table.

CUSTID SUBIDCOL1 SUBIDCOL2 SUBIDCOL3 SUBIDCOL4
Customer1 Subscriber1 Subscriber2 Subscriber3 Subscriber4
Customer2 Subscriber5 Subscriber6
Customer3 Subscriber7 Subscriber8

My problem is that normally when doing this you can use the decode function using a standard categorical field like Department. but I need to go accross using SubscriptionID, My subscription ID's are unique. We have some code to create the output table with the appropriate amount of SUBIDCOLs but not sure how to manipulate the data.

This is telecoms data, so the subscriptions are phone mobile phone subscriptions and each person can have more than one subscription.

any suggetions

Thanks

Lee

 

[THoey]

I can't tell you how to do this, but before someone else jumps in with assistance, can I ask why you need this change? It seems like your table is in a better way now then it will be after the change. Just call me curious... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[LeeFarrant]

Sure, I agree, but this data is going to a mailing house and they require 1 customer and multiple subscriptions in one record.

To tell you the truth I think someone has cocked up on the design of the datamart as you should not need to go through this hassle to meet a requirement.

Regards

Lee

 

[spenglerr]

Hi,

actually there is a description about pivoting on the Oracle Documentation CD-ROM in the Oracle 8i Data Warehousing Guide A76994-01. There is a sample provided to do s.th. like this!


you will find it also in metalink

 

[Bimalaggarwal]

Hi. Try something like this (pl7sql block) :

declare
l_stmt varchar2(20000) := null;
l_subid pls_integer;
l_id pls_integer;
l_old pls_integer;
l_count pls_integer := 0;
begin
select subid ,custid into l_subid, l_id from ( select subid ,count(custid) idd from old_table group by custid order by 2 desc) old_table where rownum < 2 order by 2;


for i in 1..l_id
loop
l_stmt := l_stmt || 'subid' ||i ||' '||'varchar2(20) ,';
end loop;
l_Stmt := substr(l_stmt, 1, length(l_stmt) -1); -- remove additional comma

l_stmt := 'create table new_table ( '|| l_stmt ||' )';

execute immediate l_stmt;

begin
for c in (select * from old_table order by 2)
count := count + 1;
if c.custid <> l_old then l_count := 1;
if count = 1 then l_stmt := 'insert into new_table(custid,subid' || l_count ||') values('||c.custid ||','||c.subid||');commit;');
else l_stmt := 'update new_table set subid'||l_count|| ' = ' || c.subid ||'where custid = '||c.custid ||';commit';
end if;
execute immediate l_stmt;
l_old := c.custid;
end;
end;
/

 

[THoey]

I understand your problem better now. I still don't think it is necessary for you to change your table design. I would just follow spenglerr's idea of creating a pivot table report and send them the data as they wish. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[LeeFarrant]

thanks for the info and the code. I am having a go at the code right now. I get the following error when running it.

I am not a wiz at this so I need a bit of direction

thanks again for taking the time out to assist me.


count := count + 1;
*
ERROR at line 24:
ORA-06550: line 24, column 5:
PLS-00103: Encountered the symbol &quot;COUNT&quot; when expecting one of the following
loop
The symbol &quot;loop&quot; was substituted for &quot;COUNT&quot; to continue.
ORA-06550: line 26, column 173:
PLS-00103: Encountered the symbol &quot&quot; when expecting one of the following:
. ( * @ % & = - + ; < / > at in mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling
The symbol &quot;(&quot;
ORA-06550: line 31, column 5:
PLS-00103: Encountered the symbol &quot;;&quot; when expecting one of the following:
if

 

[Bimalaggarwal]

Sorry for the error in the script. Try this again.
Please check the script once for correctness of variable names and syntaxes. Please make suitable changes youself.
-------------------------------------------------
declare
l_stmt varchar2(20000) := null;
l_subid pls_integer;
l_id pls_integer;
l_old pls_integer;
l_count pls_integer := 0;
begin
select subid ,custid into l_subid, l_id from ( select subid ,count(custid) idd from old_table group by custid order by 2 desc) old_table where rownum < 2 order by 2;


for i in 1..l_id
loop
l_stmt := l_stmt || 'subid' ||i ||' '||'varchar2(20) ,';
end loop;
l_Stmt := substr(l_stmt, 1, length(l_stmt) -1); -- remove additional comma

l_stmt := 'create table new_table ( '|| l_stmt ||' )';

execute immediate l_stmt;

begin
for c in (select * from old_table order by 2)
l_count := l_count + 1;
if c.custid <> l_old then l_count := 1;
if l_count = 1 then l_stmt := 'insert into new_table(custid,subid' || l_count ||') values('||c.custid ||','||c.subid||');commit;');
else l_stmt := 'update new_table set subid'||l_count|| ' = ' || c.subid ||'where custid = '||c.custid ||';commit';
end if;
execute immediate l_stmt;
l_old := c.custid;
end;
end;
/