Hi, On Mon, Jan 26, 2009 at 11:29 AM, Vikram Vaswani <bacc...@vsnl.com> wrote: > Hi Jerry > > Thanks for your input on this! > > >>> I'm using an excel library that accepts a SELECT as input and generates >>> an XLS file with the records as output. I'd like to use this where >>> possible. However I don't know if it's possible to write a SELECT that >>> compresses a 1:n relationship into a single row. Is this possible, and >>> if yes, could someone show me how? Or could you suggest another way in >>> which I could achieve the above required output? >>> >> Does whatever tool you are using have any place where you can manipulate >> the >> data between the SELECT and the creation of the XLS? If not,I think you >> need >> a user-defined function for this, or perhaps you can do it with a >> user-defined procedure. > > Unfortunately the tool doesn't let me manipulate the data. It simply reads > the result set and pops each field into a separate column in the XLS. So any > formatting I do has to be part of the SELECT. > > I did consider a procedure but the problem is that the client is still using > MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not > something they can do at this point, as they're using s shared host so the > server isn't really under their control.
I think what you're really looking for is a "pivot table" or "crosstab report" in SQL itself, right? Since you're manipulating this data in Excel, maybe you can do it there, because honestly it's better suited for that than MySQL is. But, if you need to do it in SQL, you can search the list archives -- there is something about it pretty much every week or so :) -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
