Home » SQL & PL/SQL » SQL & PL/SQL » Sql query need to pivot table format
Sql query need to pivot table format [message #185280] Mon, 31 July 2006 22:42 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
I have these set of data coming from query

Account	Amt(Base)SUM	Entity	Interco	Journal Category
70000	-23,475.44	601C	700C	Payroll
70000	-638,878.00	640C	700C	Payroll
70000	-250,054.25	650C	700C	Payroll
70000	-12,221.72	652C	700C	Payroll
70000	-164,000.25	660C	700C	Payroll
70000	-221,459.62	661C	700C	Payroll
60000	23,475.44	700C	601C	Payroll
60000	638,878.00	700C	640C	Payroll
60000	250,054.25	700C	650C	Payroll
60000	12,221.72	700C	652C	Payroll
60000	164,000.25	700C	660C	Payroll
60000	221,459.62	700C	661C	Payroll
60000	242,455.89	700C	740C	Payroll
70000	-242,455.89	740C	700C	Payroll
70000	-2,227.78	601C	700C	Purchase Invoices
70000	-2,174.71	603C	700C	Purchase Invoices
70000	-2,121.64	605C	700C	Purchase Invoices
70000	-7,955.81	640C	700C	Purchase Invoices
70000	-5,409.57	650C	700C	Purchase Invoices
60000	142.86		660C	700C	Purchase Invoices
70000	-130,695.83	660C	700C	Purchase Invoices
70000	-9,187.57	661C	700C	Purchase Invoices
60000	2,227.78	700C	601C	Purchase Invoices
60000	2,174.71	700C	603C	Purchase Invoices
60000	2,121.64	700C	605C	Purchase Invoices
60000	7,955.81	700C	640C	Purchase Invoices
60000	5,409.57	700C	650C	Purchase Invoices
60000	130,695.83	700C	660C	Purchase Invoices
60000	9,187.57	700C	661C	Purchase Invoices
70000	-142.86		700C	660C	Purchase Invoices

Can I produce the result into similar to Pivot table that speadsheet offer or format mention below:
I have tried Connect_by functionality could not be a great help.
Is there any other way to produce by sql query in format below.

Sum of Amt (Base) SUM		Journal Category		
Entity		Interco		Payroll		Purchase Invoices	Grand Total
601C		700C		-23475.44	-2227.78		-25703.22
601C Total			-23475.44	-2227.78		-25703.22
640C		700C		-638878.00	-7955.81		-646833.81
640C Total			-638878.00	-7955.81		-646833.81
650C		700C		-250054.25	-5409.57		-255463.82
650C Total			-250054.25	-5409.57		-255463.82
652C		700C		-12221.72				-12221.72
652C Total			-12221.72				-12221.72
660C		700C		-164000.25	-130552.97		-294553.22
660C Total			-164000.25	-130552.97		-294553.22
661C		700C		-221459.62	-9187.57		-230647.19
661C Total			-221459.62	-9187.57		-230647.19
700C		601C		23475.44	2227.78			25703.22
		640C		638878.00	7955.81			646833.81
		650C		250054.25	5409.57			255463.82
		652C		12221.72				12221.72
		660C		164000.25	130552.97		294553.22
		661C		221459.62	9187.57			230647.19
		740C		242455.89				242455.89
		603C		2174.71					2174.71
		605C		2121.64					2121.64
700C Total			1552545.17	159630.05		1712175.22
740C		700C		-242455.89				-242455.89
740C Total			-242455.89				-242455.89
603C		700C		-2174.71				-2174.71
603C Total			-2174.71				-2174.71
605C		700C		-2121.64				-2121.64
605C Total			-2121.64				-2121.64
Grand Total			0.00		0.00			0.00

thanks in advance.

anan
Re: Sql query need to pivot table format [message #185293 is a reply to message #185280] Tue, 01 August 2006 01:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hey, great post. Well formatted and easy to understand.

Shame you didn't bother to search first Sad

This one comes up about three times a week, seach on "Pivot".
Re: Sql query need to pivot table format [message #185501 is a reply to message #185293] Wed, 02 August 2006 01:24 Go to previous messageGo to next message
Ashokragauv
Messages: 13
Registered: June 2006
Location: Pune
Junior Member

Hi,
If he seen that similar qry before why hs is going to post this message again. Pls don't comment like this,

Re: Sql query need to pivot table format [message #185530 is a reply to message #185501] Wed, 02 August 2006 03:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

If he seen that similar qry before why hs is going to post this message again.


I guess because he didn't look to see if there were similar problems on the board.

Quote:

Pls don't comment like this,

But if we don't keep raising this point, then people will keep not looking and posting questions that the answers already exist for.
It takes time that we could usefully spend answering people with other problems.
Re: Sql query need to pivot table format [message #185551 is a reply to message #185501] Wed, 02 August 2006 05:26 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member

Quote:

If he seen that similar qry before why hs is going to post this message again.

Where does it sy that he has seen a similar query. He says that he needs a similar format to that supplied by Spreadsheets.

Quote:

Can I produce the result into similar to Pivot table that speadsheet offer


Jim
Previous Topic: How to call a procedure which will return a refcursor from another procedure?
Next Topic: How to update a Long field in Oracle
Goto Forum:
  


Current Time: Fri Dec 09 00:14:17 CST 2016

Total time taken to generate the page: 0.17099 seconds