Home » SQL & PL/SQL » SQL & PL/SQL » pivoting numbers and bring the names instead of numbers
pivoting numbers and bring the names instead of numbers [message #303111] Thu, 28 February 2008 00:43 Go to next message
prasanna_anbu
Messages: 34
Registered: November 2006
Location: india
Member
I have two tables the common column for the both tables
is no coloumn given below

table1
no name
1 n1
2 n2
3 n3
4 n4

table2
no sum diff
1 ('1','2') ('2','3')
2 ('3','2') ('1','4')
3 ('2','3') ('3','4')
4 ('1','4') ('3','4')


from the above tables i want a result given below

sum - prod
(n1+n2)-(n2+n3)
(n3+n2)-(n1+n4)
(n2+n3)-(n2+n4)
(n1+n4)-(n3+n4)

the name should be present instead of the no in the result set
can any one please help me in this

Re: pivoting numbers and bring the names instead of numbers [message #303114 is a reply to message #303111] Thu, 28 February 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post a test case: create table and insert statements.
Also post the result you want with these data.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.

Regards
Michel

Re: pivoting numbers and bring the names instead of numbers [message #303126 is a reply to message #303111] Thu, 28 February 2008 01:18 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
As n1,n2,n3 and n4 are name then what does following mean:-
(n1+n2)-(n2+n3)
(n3+n2)-(n1+n4)
(n2+n3)-(n2+n4)
(n1+n4)-(n3+n4)


regards,
Re: pivoting numbers and bring the names instead of numbers [message #303154 is a reply to message #303126] Thu, 28 February 2008 03:10 Go to previous messageGo to next message
prasanna_anbu
Messages: 34
Registered: November 2006
Location: india
Member
In the table2 the numbers are stored like
('1','2') ('2','3')
('3','2') ('1','4')
('2','3') ('3','4')
('1','4') ('3','4')
i want to substitute the name instead of these numbers

[Updated on: Thu, 28 February 2008 03:12] by Moderator

Report message to a moderator

Re: pivoting numbers and bring the names instead of numbers [message #303156 is a reply to message #303154] Thu, 28 February 2008 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 28 February 2008 07:47
Please post a test case: create table and insert statements.
Also post the result you want with these data.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.

Regards
Michel


You can do it with SUBSR and INSTR.

[Updated on: Thu, 28 February 2008 03:13]

Report message to a moderator

Re: pivoting numbers and bring the names instead of numbers [message #303159 is a reply to message #303156] Thu, 28 February 2008 03:25 Go to previous messageGo to next message
prasanna_anbu
Messages: 34
Registered: November 2006
Location: india
Member
create table t1(no number,name varchar2(20));

insert into t1 values(1,n1);

insert into t1 values(2,n2);

insert into t1 values(3,n3);

insert into t1 values(4,n4);

create table t2(no number,sum varchar2(20),diff varchar2(20));

insert into t2 values( 1,('1'+'2'),('2'+'3'));

insert into t2 values( 1,('2'+'4'),('1'+'4'));

insert into t2 values( 1,('3'+'4'),('1'+'3'));

insert into t2 values( 1,('2'+'3'),('1'+'3'));



instead of the 1 i want to substitute n1
for 2 n2
for 3 n3
for 4 n4

from the above tables i want given results the common column is no

sum-diff
--------
(n1+n2)-(n2+n3)
(n3+n2)-(n1+n4)
(n2+n3)-(n2+n4)
(n1+n4)-(n3+n4)


Re: pivoting numbers and bring the names instead of numbers [message #303160 is a reply to message #303159] Thu, 28 February 2008 03:28 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Please run your test case (as you have posted it, without alteration) then select from the 2 tables and post what you get.

[Updated on: Thu, 28 February 2008 03:29]

Report message to a moderator

Re: pivoting numbers and bring the names instead of numbers [message #303163 is a reply to message #303111] Thu, 28 February 2008 03:35 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SQL> ed
Wrote file afiedt.buf

  1*  insert into table2 values(1,('1'+'2'),('2'+'3'))
SQL> /

1 row created.

SQL> select * from table2;

        NO SUM        DIFF
---------- ---------- ----------
         1 3          5


What is it?

regards,
Re: pivoting numbers and bring the names instead of numbers [message #303164 is a reply to message #303163] Thu, 28 February 2008 03:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
What is it?

Who are you replying to and what are you trying to say?
Re: pivoting numbers and bring the names instead of numbers [message #303167 is a reply to message #303160] Thu, 28 February 2008 03:44 Go to previous messageGo to next message
prasanna_anbu
Messages: 34
Registered: November 2006
Location: india
Member
I am really sorry here i have given the senario correctly


create table t1(no number,name varchar2(20));

insert into t1 values(1,'n1');

insert into t1 values(2,'n2');

insert into t1 values(3,'n3');

insert into t1 values(4,'n4');

create table t2(no number,sum varchar2(20),diff varchar2(20));

insert into t2 values( 1,'(''1''+''2'')','(''2''+''3'')');

insert into t2 values( 1,'(''2''+''4'')','(''1''+''4'')');

insert into t2 values( 1,'(''3''+''4'')','(''1''+''3'')');

insert into t2 values( 1,'(''2''+''3'')','(''1''+''3'')');



instead of the 1 i want to substitute n1
for 2 n2
for 3 n3
for 4 n4

from the above tables i want given results the common column is no

sum-diff
--------
(n1+n2)-(n2+n3)
(n3+n2)-(n1+n4)
(n2+n3)-(n2+n4)
(n1+n4)-(n3+n4)




Re: pivoting numbers and bring the names instead of numbers [message #303169 is a reply to message #303111] Thu, 28 February 2008 03:50 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
Who are you replying to and what are you trying to say?


it was not for you.it was for prasanna_anbu and he has already replied.

regards,



Re: pivoting numbers and bring the names instead of numbers [message #303171 is a reply to message #303167] Thu, 28 February 2008 03:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
As Michel said, you can accomplish this with the substr function.
A very small demo.
with t as (select '(''1''+''2'')' x from dual)
select substr(x,1,2)||'n'||substr(x,3) from t

HOWEVER, my betting is that this will not match your requirements quite properly because I believe that you have not actually told us what you really need to do. Rather you have provided (what I bet is) an over simplified example.
Re: pivoting numbers and bring the names instead of numbers [message #303173 is a reply to message #303171] Thu, 28 February 2008 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think it is also easely possible with REPLACE.

Regards
Michel
Re: pivoting numbers and bring the names instead of numbers [message #303175 is a reply to message #303173] Thu, 28 February 2008 04:14 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Thu, 28 February 2008 10:06
I think it is also easely possible with REPLACE.

Regards
Michel


Of Course! Took me a furrowed Browed second or two, but now I see it. (I was looking at the 'numbers', not the chars)
Previous Topic: Help me
Next Topic: problem with where condition including range of date?
Goto Forum:
  


Current Time: Tue Dec 06 00:07:25 CST 2016

Total time taken to generate the page: 0.20551 seconds