Solved

Query formation

Posted on 2012-04-09
8
246 Views
Last Modified: 2012-06-22
Hi,

1. main query.sql file attached

2. mainquery result set is attached

3. Required format of the result set attached in the excel "TO BE RESULT SET.XLS.xlsx"

Please help to achive this format.

Thanks
Main-query.sql
mainquery-result-set.xlsx
TO-BE-RESULT-SET.XLS.xlsx
0
Comment
Question by:neoarwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 37823578
Because they appear to be left_justified, may we assume that date_only, sector, month, and month_nr as of character datatype?
0
 
LVL 32

Expert Comment

by:awking00
ID: 37823593
cmpgn_yr as well?
0
 
LVL 32

Expert Comment

by:awking00
ID: 37823735
What version of Oracle are you using?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 37823784
Try this
select rtrim(xmlagg(xmlelement(e,date_only||',')).extract('//text()'),',') date_only
      ,rtrim(xmlagg(xmlelement(e,res_cell||',')).extract('//text()'),',') res_cell
      ,sector
      ,region
      ,month
      ,month_nr
      ,cmpgn_yr
      ,division
from (<your main query>)
group by sector, region, month, month_nr, cmpgn_yr, division;
0
 

Author Comment

by:neoarwin
ID: 37824148
Hi,

Sorry for the delayed reply.

My oracle version is 10g.

Regards
0
 
LVL 32

Expert Comment

by:awking00
ID: 37824508
Then my last response should work.
0
 

Author Comment

by:neoarwin
ID: 37826668
Please check the attachd images.
The order of date_only is not matching with order of res_cell..

Please help resolve, this is the same problem I am facing when using wm_concat as well.
results-obtained.JPG
Required-result-order.JPG
0
 

Author Closing Comment

by:neoarwin
ID: 37826846
Thank you
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question