Home » SQL & PL/SQL » SQL & PL/SQL » pivot concept doubt
| pivot concept doubt [message #258225] |
Fri, 10 August 2007 10:44  |
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 #258232 is a reply to message #258229] |
Fri, 10 August 2007 11:21  |
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 #258237 is a reply to message #258234] |
Fri, 10 August 2007 11:47 |
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 #258249 is a reply to message #258246] |
Fri, 10 August 2007 12:30 |
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 |
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 #258252 is a reply to message #258251] |
Fri, 10 August 2007 12:55 |
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 #258257 is a reply to message #258254] |
Fri, 10 August 2007 13:05 |
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 #258422 is a reply to message #258232] |
Sat, 11 August 2007 17:41 |
William Robertson
Messages: 1643
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.
|
|
|
|
Goto Forum:
Current Time: Mon Dec 02 07:09:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
