OraFAQ Forum: SQL & PL/SQL » Dynamic columns in crosstab output

Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic columns in crosstab output

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dynamic columns in crosstab output [message #204341]

Mon, 20 November 2006 04:04

monasingh
Messages: 229
Registered: May 2006
Location: Mumbai

Senior Member

Hi,
I am working on a sql file which needs to give a report like a cross-tab report. I have searched the forum but couldn't get how to code for a dynamic column.
Like my column will be generated dynamically at runtime. This is a date column. The column can be of any number with a 7 days interval.

			    11/11/2006	11/18/2006  11/25/2006	     12/2/2006	     12/9/2006	      Total
	1220322	TEXPA FITTED	292	20,361	       45,396	        71,636	        7,040	      144,725
1.00	Total		        292	20,361	       45,396	        71,636	        7,040	      144,725
	1220319	MAN FIT	        0	1,118	       600	        650	        0	      2,368
	1220320	MAN FITTED	0	300	       450	        650	        0	      1,400
2.00	Total		        0	1,418	       1,050	        1,300	        0	      3,768
	1220345	TEXPA FLAT	0	4,654	       62,853	        64,023	        11,846	      143,376
	1220356	TEXPA FLT INST	0	150	       1,439	        1,218	        0	      2,807
3.00	Total		        0	4,804	       64,292	        65,241	        11,846	      146,183
	1220388	PILLOWCASE	0	9,466	       96,396	        54,358	        0	      160,220
	1220395	AUTOMATED	0	3,768	       3,311	        1,020	        0	      8,099
4.00	Total		        0	13,234	       99,707	        55,378	        0	      168,319
Grand Total			292	39,817	       210,445	        193,555	        18,886	      462,995

I know the start-date and end-date and the date columns are displayed at an interval of 7 days starting from the startdate. There are three other columns.
First column gives an ID, second gives respid and third gives description filed data. The data under date columns is the quantity data based on a date columnwise and on ID, respID and Descr row wise.
The dates column are created dynamically and the data needs to be displayed under them dynamically.
Please give me a hint to work on this.

Thanks,
Mona

[Updated on: Mon, 20 November 2006 04:06]

Report message to a moderator

Re: Dynamic columns in crosstab output [message #209310 is a reply to message #204341]

Thu, 14 December 2006 04:10

surinderx
Messages: 1
Registered: December 2006
Location: DUBAI

Junior Member

Mona Ji,

create two tables (one for heading part - Dates) and another for detail part.
This is a matrix report which u r going to import/ generate.
In matrix report we need 4 groups and minimum 2 tables for distributing the database.

rgds
Kapoor

Report message to a moderator

Previous Topic:	Advance Queing and tracking transactions
Next Topic:	How to find Oracle path

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 07:36:14 CST 2024