From: Peter Brawley
Date: November 15 2012 2:06am
Subject: Re: Dynamic crosstab got me lost.
List-Archive: http://lists.mysql.com/mysql/228633
Message-Id: <50A44E1F.3040808@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------090001040202010503010706"

--------------090001040202010503010706
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.
>>
>>
>


--------------090001040202010503010706--