List:General Discussion« Previous MessageNext Message »
From:nwood Date:April 28 2010 10:55am
Subject:Re: Pivot Query in
View as plain text  
On Wed, 2010-04-28 at 15:24 +0530, VR Venugopal Rao wrote:
> I am working on Java-HTML-MS Access Backend database.
> 
> Now office is removing all unlicensed softwares and they are removing
> microsoft applications also and hence the necessity to shift from MS Access
> to MySQL has arised.
> 
> In MS Access I used to generate one pivot query which gets the following
> report from the database which contains the following fields:
> 
> Date, ProjectCode Building, Number of Copies
> 
>  
> 
> I want to get a Connsolidate Report of 
> 
> Project Code   R&D   STP
> 
> 1007304----------04-------04
> 
> (Group by Project Code)(Sumtotal Building wise).
> 
>  
> 
> I have tried to execute the following code :
> 
> select pcode, building, sum(ncopies) from request group by pcode,building
> 
>  
> 
> It is giving the following repott
> 
> 1007304--R&D--04
> 
> 1007304-STP--05
> 
> Here there is a repetition of pcode,
> 
>  
> 
> How can I avoid this. 

Don't use unlicenced commercial software in the first place? 

> I want to get the Building Name on top as Column Names
> and bottom I should get the Count.
> 

Unlike MS Access MySQL does not directly support 'pivot queries', the
portable equivalent is a cross-tab query. In order to allow for the
addition and removal of departments you'll want to dynamically generate
the query each time in a client application or  a stored procedure

Crosstab queries in MySQL
http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html

Crosstab queries and their native support in MS Access
http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25

HTH 

Nigel

Thread
Pivot Query in VR Venugopal Rao28 Apr
  • Re: Pivot Query innwood28 Apr
  • RE: Pivot Query in Jay Blanchard28 Apr
    • Re: Pivot Query inPeter Brawley28 Apr
    • Pivot Query in MySQLVR Venugopal Rao29 Apr
    • How to Get Running Totals.Venugopal Rao3 May
      • Re: How to Get Running Totals.Peter Brawley3 May