Re: oracle cross tabulation queries

From: Charles McDonald <cmcdon12_at_ford.com>
Date: Wed, 23 Jun 1999 12:57:56 +0100
Message-ID: <3770CBC4.CA433714@ford.com>

A copy of a query I use to create crosstab

'***********************

SELECT 	I4.Market, SA.SalesAreaDescription  MarketDescription,
	I4.Body, F1.FeatureDescription BodyDescription,
	I4.Series, F2.FeatureDescription SeriesDescription,
	ENCG_TRDJ, ENCG_TRRP, ENCH_TRDJ, ENCH_TRRP, ENCJ_TRDJ, ENCJ_TRRP, 
	END5_TRDJ, END5_TRRP, END6_TRDJ, END6_TRRP, ENLC_TRDJ, ENLC_TRRP
FROM	(SELECT   I3.Market, I3.Body, I3.Series,  
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'ENCG_TRDJ',Mix,0))
ENCG_TRDJ, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'ENCG_TRRP',Mix,0))
ENCG_TRRP, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'ENCH_TRDJ',Mix,0))
ENCH_TRDJ, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'ENCH_TRRP',Mix,0))
ENCH_TRRP, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'ENCJ_TRDJ',Mix,0))
ENCJ_TRDJ, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'ENCJ_TRRP',Mix,0))
ENCJ_TRRP, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'END5_TRDJ',Mix,0))
END5_TRDJ, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'END5_TRRP',Mix,0))
END5_TRRP, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'END6_TRDJ',Mix,0))
END6_TRDJ, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'END6_TRRP',Mix,0))
END6_TRRP, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'ENLC_TRDJ',Mix,0))
ENLC_TRDJ, 
		  SUM(DECODE((I3.Engine || '_' || I3.Transmission),'ENLC_TRRP',Mix,0))
ENLC_TRRP
	FROM     (SELECT  I2.ItemCode, I2.Market, I2.Body, I2.Series,
I2.Engine, I2.Transmission, M.Mix     
	         FROM     (SELECT  I.ItemCode, 
	                          

MAX(DECODE(I.FeatureFamily,'WAE',I.FeatureCode,'WSE',I.FeatureCode, Null)) Market,                                   

MAX(DECODE(I.FeatureFamily,'BS',I.FeatureCode, Null)) Body,                                   

MAX(DECODE(I.FeatureFamily,'VS',I.FeatureCode, Null)) Series,

	                          
MAX(DECODE(I.FeatureFamily,'EN',I.FeatureCode, Null)) Engine,     
				   MAX(DECODE(I.FeatureFamily,'TR',I.FeatureCode, Null))
Transmission      
	                  FROM     (SELECT  EAO.ItemCode, ES.FeatureFamily,
ES.FeatureFamilyCode,
	                                    (ES.FeatureFamily ||
ES.FeatureFamilyCode) FeatureCode           
	                           FROM     MARGMOD.tblEntitySpec ES,
	                                    MARGMOD.tblEntityAndOption
EAO             
	                           WHERE    EAO.ProgramCode =


'CD132'              


	                             AND    EAO.ProgramYear = '2001'     
	                             AND    EAO.EntityGroup = 'TEST'
	                             AND    EAO.EntityType = 'ENT'
	                             AND    EAO.ItemCode = ES.ItemCode   
	                             AND    ES.FeatureFamily IN
('WAE','BS','VS','EN','TR')
	                           ) I    
	                  GROUP BY I.ItemCode       
	                  ) I2,
	                  MARGMOD.tblMix M    
	         WHERE    I2.Market IN ('WAEDX','WAENL')   
	           AND    M.ItemCode (+) = I2.ItemCode 
	           AND    M.MixGroup (+) = 'MIX4' 
	         ) I3 
	GROUP BY I3.Market, I3.Body, I3.Series
	) I4,
	MARGMOD.tblFeature F1,
	MARGMOD.tblFeature F2,
	MARGMOD.tblSalesArea SA
WHERE	SA.ProgramCode = 'CD132' 
  AND	SA.ProgramYear = '2001' 
  AND	SA.SalesAreaCode = I4.Market
  AND	F1.ProgramCode = 'CD132'
  AND	F1.ProgramYear = '2001'
  AND	F1.FeatureCode = I4.Body
  AND	F2.ProgramCode = 'CD132'
  AND	F2.ProgramYear = '2001'
  AND	F2.FeatureCode = I4.Series

ORDER BY I4.Market, I4.Body, I4.Series

'******************************

Now this SQL must be built at runtime depending on the selections of the user.

Hope it helps

Charles

PR1999 wrote:

> 
> Would like to know if there is any way to create and sql statement that
> performs a cross tab in oracle.
> 
> I know that it can be done using oracle reports.  But I am interested to see if
> it can be executed with a single statement.
> 
> Thanks

Received on Wed Jun 23 1999 - 06:57:56 CDT