I think what you are looking for is something like this: ---------------------------------------------------------------------
SELECT CONCAT(A.FIELD1,',',GROUP_CONCAT(B.FIELD2)) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.ID=B.ID_TABLE1 --------------------------------------------------------------------- Cheers Claudio 2009/1/26 Baron Schwartz <ba...@xaprb.com> > 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=claudio.na...@gmail.com > >
