Home » SQL & PL/SQL » SQL & PL/SQL » Grouping and Merging Data
Grouping and Merging Data [message #204126] Sat, 18 November 2006 00:13 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hello guys,

I need some help over here, actually i have a table which contains some data on customers and their phone numbers, something like below

CusNo PhoneNum
001 012545888
002 187856988
001 012555566

So the problem is that, how do i write a query to display the following output?

CusNo PhoneNum
001 012545888,012555566
002 187856988

So what it does is basically it combines the Phone Number for the same customer separated by a comma.

Im aware that i can write a pl/sql function which takes in CusNo and returns their phone number and use this function in my query, but i was just wondering if there is a way to do this without making use of user defined function.

thank you very much for any input/ideas

[Updated on: Sat, 18 November 2006 01:32]

Report message to a moderator

Re: Grouping and Merging Data [message #204149 is a reply to message #204126] Sat, 18 November 2006 03:57 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
use the concat operator
Re: Grouping and Merging Data [message #204203 is a reply to message #204149] Sat, 18 November 2006 21:18 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hello Amul,
thanks for the hint, appreciate if you could elaborate a bit more. Because in my situation, the phone number can exists many times, its not limited to only 2 numbers, this is what i can think of at the moment, any ideas to get me started from here would be useful.
Im not sure how do i get some kind of looping functionality so that i can make use of the concatenation keyword

select cusNo, count(phoneNum),
CASE
  when count(phoneNum) > 1 then 
     (select phoneNum from customer where cusNo = cusNo)
  else
     (select phoneNum from customer where cusNo = cusNo)

  end phoneNum
from customer
group by cusNo, phoneNum

[Updated on: Sat, 18 November 2006 21:42]

Report message to a moderator

Re: Grouping and Merging Data [message #204216 is a reply to message #204203] Sun, 19 November 2006 01:55 Go to previous message
sudhir.sukumar
Messages: 52
Registered: August 2005
Location: India
Member

Assuming max. of 8 phone numbers exists for a customer, here we go.

select distinct x.customer_number, 
       max(decode(x.rn, 1, phone_number)) val1,
       max(decode(x.rn, 2, phone_number)) val2,
       max(decode(x.rn, 3, phone_number)) val3,
       max(decode(x.rn, 4, phone_number)) val4,
       max(decode(x.rn, 5, phone_number)) val5,
       max(decode(x.rn, 6, phone_number)) val6,
       max(decode(x.rn, 7, phone_number)) val7,
       max(decode(x.rn, 8, phone_number)) val8
from (select your_table.customer_number,
       your_table.phone_number,
	   row_number()
	   over (partition by your_table.customer_number
	   order by your_table.customer_number, your_table.phone_number desc) rn
	   from your_table) x
group by x.customer_number 


Plenty of examples could be found on this forum if you search.

[Updated on: Sun, 19 November 2006 01:57]

Report message to a moderator

Previous Topic: using partition name with date function to_char(sysdate,'ddmmyy')
Next Topic: Explict Cursor
Goto Forum:
  


Current Time: Sun Dec 04 20:22:48 CST 2016

Total time taken to generate the page: 0.12602 seconds