Home » SQL & PL/SQL » SQL & PL/SQL » SQL AGGREGATE AND FORMAT QUESTION
icon1.gif  SQL AGGREGATE AND FORMAT QUESTION [message #197785] Thu, 12 October 2006 12:48 Go to next message
sameer24
Messages: 3
Registered: October 2006
Location: washington dc
Junior Member
Hi folks,
I have a question with formating sql output. May be some one has done this before. I am using analytical functions i.e roolup to get subtotal and totals. What i need is my report to look like below.

My output looks like this

YEAR PRODUCT TITLE PRICE REGION
1997 B1 BOOK! 2 VA
1997 B2 BOOK2 6 MD
1997 SUBTOTAL 8
1998 B2 BOOK1 2 VA
1998 SUBTOTAL 2
2000 B1 BOOK1 6 va
2000 B2 BOOK2 4 MD
2000 SUBTOTAL 10


I want to see the output like

YEAR PRODUCT TITLE PRICE REGION
1997 B1 BOOK! 2 VA
B2 BOOK2 6 MD
SUBTOTAL 8
1998 B2 BOOK1 2 VA
SUBTOTAL 2
2000 B1 BOOK1 6 va
B2 BOOK2 4 MD
SUBTOTAL 10

PLEASE help,

Thanks in advance
sameer
Re: SQL AGGREGATE AND FORMAT QUESTION [message #197787 is a reply to message #197785] Thu, 12 October 2006 13:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Prior to your query, you should issue:

break on year
Re: SQL AGGREGATE AND FORMAT QUESTION [message #197788 is a reply to message #197787] Thu, 12 October 2006 13:12 Go to previous messageGo to next message
sameer24
Messages: 3
Registered: October 2006
Location: washington dc
Junior Member
THANKS buddy but i have this sql in pl/sql block.
Re: SQL AGGREGATE AND FORMAT QUESTION [message #197791 is a reply to message #197788] Thu, 12 October 2006 13:42 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You might try wrapping this around your query:

select decode(row_number() over (parition by year order by year), 1, year) year,
	product,
	title,
	price,
	region from (
	<your_query_here>);
icon14.gif  Re: SQL AGGREGATE AND FORMAT QUESTION [message #197792 is a reply to message #197791] Thu, 12 October 2006 13:49 Go to previous message
sameer24
Messages: 3
Registered: October 2006
Location: washington dc
Junior Member
Thank you very much this solved my problem.
Cheers
Sameer
Previous Topic: Help needed
Next Topic: How to flatten a hierarchy
Goto Forum:
  


Current Time: Fri Dec 09 19:26:06 CST 2016

Total time taken to generate the page: 0.11099 seconds