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
