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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
