List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:January 27 2009 9:20am
Subject:Re: Help with formatting of 1:n relationship
View as plain text  
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 <baron@stripped>

> Hi,
>
> On Mon, Jan 26, 2009 at 11:29 AM, Vikram Vaswani <bacchus@stripped> 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=1
>
>

Thread
Help with formatting of 1:n relationshipVikram Vaswani26 Jan
  • RE: Help with formatting of 1:n relationshipJerry Schwartz26 Jan
    • Re: Help with formatting of 1:n relationshipVikram Vaswani26 Jan
      • Re: Help with formatting of 1:n relationshipBaron Schwartz26 Jan
        • Re: Help with formatting of 1:n relationshipVikram Vaswani26 Jan
        • Re: Help with formatting of 1:n relationshipClaudio Nanni27 Jan
          • Re: Help with formatting of 1:n relationshipWalter Heck27 Jan
      • RE: Help with formatting of 1:n relationshipJerry Schwartz26 Jan