Oracle query to fetch data in single data

Question

1.00/5 (1 vote)

See more:

00089079/3	38956761	A481411	UKR	PAROYYK	YARIY	4/13/2017 6:03:48 PM	0	Success:Account already exists at CRM.AccountId:1-3X9KAL2.The request is accepted for Order Creation at CRM	SIMSIM	RESELLER						Reseller	
00089079/3	38979318	A481411	UKR	PAROYYK	YARIY	4/13/2017 6:01:51 PM	0	Success:Account already exists at CRM.AccountId:1-3X9KAL2.The request is accepted for Order Creation at CRM	SIMSIM	RESELLER						Reseller	
00089079/3	39892709	A481411	UKR	PAROYYK	YARIY	4/13/2017 5:55:15 PM	0	Success:Account already exists at CRM.AccountId:1-3X9KAL2.The request is accepted for Order Creation at CRM	SIMSIM	RESELLER						Reseller	
00089079/3	38025192	A481411	UKR	PAROYYK	YARIY	4/13/2017 5:53:34 PM	0	Success:Account already exists at CRM.AccountId:1-3X9KAL2.The request is accepted for Order Creation at CRM	SIMSIM	RESELLER						Reseller	
00089079/3	32118459	A481411	UKR	PAROYYK	YARIY	4/13/2017 5:52:17 PM	0	Success:Account already exists at CRM.AccountId:1-3X9KAL2.The request is accepted for Order Creation at CRM	SIMSIM	RESELLER						Reseller	
00089079/3	32379150	A481411	UKR	PAROYYK	YARIY	4/13/2017 5:50:49 PM	0	Success:Account already exists at CRM.AccountId:1-3X9KAL2.The request is accepted for Order Creation at CRM	SIMSIM	RESELLER						Reseller	
00089079/3	33279150	A481411	UKR	PAROYYK	YARIY	4/13/2017 5:48:20 PM	21	Fail:ServiceID:97333279150  is not available at CRM	SIMSIM	RESELLER						Reseller	
00089079/3	38731029	A481411	UKR	PAROYYK	YARIY	4/13/2017 5:47:09 PM	0	Success:Account already exists at CRM.AccountId:1-3X9KAL2.The request is accepted for Order Creation at CRM	SIMSIM	RESELLER						Reseller	
00089079/3	38917455	A481411	UKR	PAROYYK	YARIY	4/13/2017 5:45:55 PM	0	Success:Account already exists at CRM.AccountId:1-3X9KAL2.The request is accepted for Order Creation at CRM	SIMSIM	RESELLER						Reseller	
00089079/3	39146100	A481411	UKR	PAROYYK	YARIY	4/13/2017 5:38:33 PM	0	Transaction has been completed successfully:Account Creation was successful at CRM.AccountId:1-3X9KAL2.The request is accepted for Order Creation at CRM	SIMSIM	RESELLER						Reseller

All the datas under one 00089079/3 but different mobile nos.Please help to get data using oracle query so that datas will be as single row but mobile no will display as(m1,m2,m3..)

What I have tried:

I Used LISTAGG but its not fetching

Posted 4-Apr-18 2:43am

Member 11658469

Updated 4-Apr-18 3:09am

CHill60

v2

Add a Solution

Comments

CHill60 4-Apr-18 9:02am

Post the code that you used, "I used LISTAGG but its not fetching" is not a useful description of either your code or your problem

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-04-04T03:10:00

Solution 1

You are trying to create a "comma separated list" from multiple rows.

The "best" way of doing this in Oracle depends on the version you have installed. As you have said "LISTAGG but its not fetching" I can only assume that the version you are using is prior to 11G Release 2.

On this thread[^] there are alternative several methods listed, I suggest starting with the accepted solution.

Posted 4-Apr-18 3:10am

CHill60

Comments

Member 11658469 4-Apr-18 9:14am

I tried the Listagg with comma separator but the output I got is

Col1 Col2 Col3
00089079/3,00089079/3 39146100,38917455 A481411,A481411

Here Col2 is different but Col1 & Col3 are same.I need output as
00089079/3 39146100,38917455 A481411

CHill60 4-Apr-18 10:38am

Post the code that you used

Member 11658469 5-Apr-18 1:32am

SELECT LISTAGG(REGISTEREDMOBILENO, ',') WITHIN GROUP (ORDER BY CUSTOMERID) AS REGISTEREDMOBILENO,LISTAGG(CPRCR, ',') WITHIN GROUP (ORDER BY CPRCR) AS CPRCR
FROM (SELECT DISTINCT RRL.MOBILENO AS REGISTEREDMOBILENO, RS.CPRCR FROM RESELLER_REGISTRATION_LOG RRL
LEFT OUTER JOIN RESELLER_SECURITY RS ON RRL.REGISTEREDBY = RS.CPRCR WHERE RRL.LOGDATE BETWEEN to_date('04/01/2017', 'mm/dd/yyyy') AND to_date('04/30/2017', 'mm/dd/yyyy')) GROUP BY CPR

Here the mobile no is different for example if we have 3 mobile nos then CPR will also fetch 3 times .If different CPR need to fetch different

For ex:

Mob CPR
MOb1,Mob2,Mob3 CPR1

If 2 CPR will be there need the output like that

MOB CPR
MOB1,MOB2,MOB3 CPR1,CPR2

CHill60 5-Apr-18 6:43am

So you only ever want a single line returned where column1 is a list of mobile numbers and column2 is a list of CPR. Weird, but if that is what you want then you are probably going to need to UNION two queries - one to get the list of Mobile numbers and the other to get the list of CPR. You can then JOIN those sub-queries to get the data in the format you want.
I can't access either SQL Fiddle nor Oracle at the moment so I can't post the code to it - sorry