List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:November 15 2012 2:06am
Subject:Re: Dynamic crosstab got me lost.
View as plain text  
On 2012-11-14 5:08 PM, Mogens Melander wrote:
> Thanks Rick
>
> Yes, I know it's not going the most (in lack of descriptive words) pretty
> piece of code. The variable number of columns is the key phrase here.
>
> I've already told them, this is not a SQL task, but having infinite
> trust in my peers "out there" I know it can be done. I just fail
> to do the logic myself. Believe me, I tried.

Some ideas under "Automate pivot table queries" at 
http://www.artfulsoftware.com/infotree/queries.php. 
<http://www.artfulsoftware.com/infotree/queries.php>

PB

-----

>
> On Wed, November 14, 2012 16:49, Rick James wrote:
>> While it is possible to pivot a table like that, the code is ugly, and
>> does not work well if you need a variable number of columns.
>>
>> SQL can do the JOIN to get a single resultset for the information, but the
>> layout is best done in a application language, such as PHP.
>>
>>> -----Original Message-----
>>> From: Mogens Melander [mailto:mogens@stripped]
>>> Sent: Wednesday, November 14, 2012 12:23 AM
>>> To: mysql@stripped
>>> Subject: Dynamic crosstab got me lost.
>>>
>>> Guru's
>>>
>>> I've been tasked with creating a report with information about which
>>> images
>>> are related to each active article. The images are descriptive icons
>>> visualizing different properties.
>>>
>>> Articles can be active 'on' or not '', and only active is to be included
>>> in
>>> the result.
>>>
>>> The number of available icons changes constantly, and are too many, to
>>> use
>>> anything static.
>>>
>>> What I got is:
>>>
>>> Table: main
>>> -----------------
>>> code (int)
>>> active (on/off)
>>> -----------------
>>>
>>> Table: icon
>>> -----------------
>>> id (int)
>>> filename (char)
>>> -----------------
>>>
>>> Table: iconrel
>>> -----------------
>>> code (int)
>>> icon (int)
>>> -----------------
>>>
>>> What I need is something like:
>>>
>>> code	filename1	filename2	filename3	filenameN
>>> 1111	on		off		off		on
>>>
>>> Hopefully somebody out there got more of a clue than I do.
>>>
>>> Any ideas, anybody?
>>>
>>> --
>>> Mogens Melander
>>> +66 8701 33224
>>>
>>> --
>>> This message has been scanned for viruses and
>>> dangerous content by MailScanner, and is
>>> believed to be clean.
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by MailScanner, and is
>> believed to be clean.
>>
>>
>


Thread
Dynamic crosstab got me lost.Mogens Melander14 Nov
  • RE: Dynamic crosstab got me lost.Rick James14 Nov
  • RE: Dynamic crosstab got me lost.Mogens Melander14 Nov
    • Re: Dynamic crosstab got me lost.Peter Brawley15 Nov
  • Re: Dynamic crosstab got me lost.Mogens Melander19 Nov
    • Re: Dynamic crosstab got me lost.hsv20 Nov
Re: Dynamic crosstab got me lost.Jan Steinman19 Nov
  • Re: Dynamic crosstab got me lost.Mogens Melander19 Nov
    • Re: Dynamic crosstab got me lost.Shawn Green19 Nov
      • Re: Dynamic crosstab got me lost.Mogens Melander20 Nov
  • Re: Dynamic crosstab got me lost.hsv19 Nov