Home » SQL & PL/SQL » SQL & PL/SQL » pivot concept doubt
pivot concept doubt [message #258225] Fri, 10 August 2007 10:44 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

table TMP11 having two column one is name and phone number. one name can have more then one phone number. now i am trying to implement pivot concept and display all the phon number in one row according to name .i wrote the following query based on earlier post.

SELECT 
  max(decode(new_column,
  1,
  PHON))||DECODE(max(decode(new_column,
  2,
  PHON)),
  NULL,
  NULL,
  ',
  '||max(decode(new_column,
  2,
  PHON)))||DECODE(max(decode(new_column,
  3,
  PHON)),
  NULL,
  NULL,
  ',
  '||max(decode(new_column,
  3,
  PHON)))
FROM 
  (select name,
  phon,
  row_number() over(partition by Name order by phon) new_column from tmp11)


it is working fine ..but i am not able to figure out how outer select statement is working. How it is combining the rows. please explain me.

-Yash
Re: pivot concept doubt [message #258229 is a reply to message #258225] Fri, 10 August 2007 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time post one letter per line.
This is unreadable, format it correctly maybe you can then understand it.

Regards
Michel
Re: pivot concept doubt [message #258232 is a reply to message #258229] Fri, 10 August 2007 11:21 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hey you guys having always problem..i just past the query on toad right click the mouse and formatting tools->format code..what ever output comes i past here ..

select max(decode(new_column,1,PHON))||DECODE(max(decode(new_column,2,PHON)),NULL,NULL,','||max(decode(new_column,2,PHON)))||DECODE(max(decode(new_column,3,PHON)),NULL,NULL,','||max(decode(new_column,3,PHON))) from (select name,phon,row_number() over(partition by Name order by phon) new_column from tmp11)


don't ask me it is 80 character log.

Now can you explain me

--Yash
Re: pivot concept doubt [message #258234 is a reply to message #258225] Fri, 10 August 2007 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Now can you explain me
Nope, not yet.

Have you searched this forum for "PIVOT QUERY"?

[Updated on: Fri, 10 August 2007 11:45] by Moderator

Report message to a moderator

Re: pivot concept doubt [message #258237 is a reply to message #258234] Fri, 10 August 2007 11:47 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Ok No problem ..it will take some more time. I will understand my self.. it is not big deal either i will post some other forum .....Thx ..

Maheer asked me why you want other forum names..thats why i need the name of some other forum ..
Re: pivot concept doubt [message #258238 is a reply to message #258232] Fri, 10 August 2007 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is more than 80 characters.
If you don't want answer from here, it is easier to no more post.

Regards
Michel
Re: pivot concept doubt [message #258240 is a reply to message #258238] Fri, 10 August 2007 11:59 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
You are the moderator or you are the only person who can replay this forum..If Yes Then i will not post anything here..

--Yash
Re: pivot concept doubt [message #258243 is a reply to message #258240] Fri, 10 August 2007 12:11 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/2866/0/
Re: pivot concept doubt [message #258246 is a reply to message #258240] Fri, 10 August 2007 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You missed my point.
I'd be glad to help you but... I want you to help me help you.
It's easy; click on Preview button, read what you post. Is this clear? Is this easy to read? Is this understandable? Good!
It just a matter of respect.

Regards
Michel
Re: pivot concept doubt [message #258249 is a reply to message #258246] Fri, 10 August 2007 12:30 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
exactly It just a matter of respect.

Quote:
Next time post one letter per line.


What you think we are asking question here so you can replay any thing bcz you are having good knowledge in oracle we are in learning phase. we are expecting help from this forum so we don’t have any respect you can replay any thing..


--Yash
Re: pivot concept doubt [message #258250 is a reply to message #258249] Fri, 10 August 2007 12:40 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you sure your query is working. I would really doubt it. Please find my observation.
with tmp
as
(select 'A' name, 123456 phone from dual
union
select 'A',456789 FROM dual
union
select 'b',4678988 from dual
union
select 'b',7895615 from dual
union
select 'b',78941324 from dual
union
select 'c',4564872 from dual
union
select 'c',4564876 from dual
union
select 'd',7851545 from dual
)
select max(decode(new_column,1,phone))
       ||
       DECODE(max(decode(new_column,2,phone)),NULL,NULL,','||max(decode(new_column,2,phone)))
       ||
       DECODE(max(decode(new_column,3,phone)),NULL,NULL,','||max(decode(new_column,3,phone))) as output
from
    (select name,phone,row_number() over(partition by Name order by phone) new_column from tmp)

OUTPUT
-------------------------------------------
7851545,7895615,78941324


Is this the expected output ?
Re: pivot concept doubt [message #258251 is a reply to message #258249] Fri, 10 August 2007 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you say and what this repeating "replay" means.
I just ask you to post thing to be easy to read. Can't you understand that?
You ask, you have to first show respect.
If you show respect, you will have respect in return.

Regards
Michel
Re: pivot concept doubt [message #258252 is a reply to message #258251] Fri, 10 August 2007 12:55 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
S.Rajaram!! Yes it is my expected output


Michel!! i am trying to give you extra respect that’s why i
formatted query on toad. that is my assumption
toad can format the code in more readable way.
if it is not then what can i do ..

If you are not able to read this code You can ask this thing in simpler way. You don't think so?

Btw i am leaving office when i will back i will replay you..keep posting

--Yash
Re: pivot concept doubt [message #258254 is a reply to message #258252] Fri, 10 August 2007 12:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If that is your expected output i don't know what to say. Good luck.
Re: pivot concept doubt [message #258257 is a reply to message #258254] Fri, 10 August 2007 13:05 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
thats is my query

select name, max(decode(new_column,1,PHON))
||DECODE(max(decode(new_column,2,PHON)),NULL,NULL,','
||max(decode(new_column,2,PHON)))
||DECODE(max(decode(new_column,3,PHON)),NULL,NULL,','||max(decode(new_column,3,PHON)))
from (select name,PHON,row_number() over(partition by name
order by PHON ) new_column from tmp11 ) group by name


thats is my output

Vikranth,12,13
Yash,11,12



Re: pivot concept doubt [message #258258 is a reply to message #258257] Fri, 10 August 2007 13:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
As others already suggested either you can search in this forum for pivot or refer this url http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740. It is been discussed enough with different set of combinations

P.S : don't get me wrong. Don't your find your recent post with the query is more readable than the others.

[Updated on: Fri, 10 August 2007 13:09]

Report message to a moderator

Re: pivot concept doubt [message #258422 is a reply to message #258232] Sat, 11 August 2007 17:41 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
ammishra wrote on Fri, 10 August 2007 17:21
Hey you guys having always problem..i just past the query on toad right click the mouse and formatting tools->format code..what ever output comes i past here ..

select max(decode(new_column,1,PHON))||DECODE(max(decode(new_column,2,PHON)),NULL,NULL,','||max(decode(new_column,2,PHON)))||DECODE(max(decode(new_column,3,PHON)),NULL,NULL,','||max(decode(new_column,3,PHON))) from (select name,phon,row_number() over(partition by Name order by phon) new_column from tmp11)


don't ask me it is 80 character log.

Now can you explain me

--Yash

Wow, you have a $1,465 development tool and that's the best formatting it can give you?

From what I remember last time I had the misfortune to use TOAD it does have preference settings, although there are no formatting tools that work properly anyway.

There is a difference between taking the trouble to format your code so people you are asking to help you can understand it, and just hitting the "Format" button in some crappy IDE without checking the preferences, then telling people to get lost if they don't like it.
Previous Topic: Oracle SQL error
Next Topic: Convert CLOB to BLOB
Goto Forum:
  


Current Time: Wed Dec 07 04:37:10 CST 2016

Total time taken to generate the page: 0.09234 seconds