List:General Discussion« Previous MessageNext Message »
From:Mogens Melander Date:November 19 2012 4:05am
Subject:Re: Dynamic crosstab got me lost.
View as plain text  
On Wed, November 14, 2012 09:23, Mogens Melander wrote:
> 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?
>

So, I got a little further with my problem. I found an article
on:

http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab

Describing how to do the dynamic generation of SQL statements. That's
all good, kind of. The resulting SQL looks like this:

SELECT main.code
, IF(iconstandardrel.icon = 4,1,0) AS 'internationalt_produkt.eps'
, IF(iconstandardrel.icon = 3,1,0) AS 'god_vaerdi.eps'
, IF(iconstandardrel.icon = 2,1,0) AS 'for_miljoeets_skyld.eps'
, IF(iconstandardrel.icon = 1,1,0) AS 'ergonomisk_produkt.eps'
, IF(iconstandardrel.icon = 6,1,0) AS 'saml_selv.eps'
, IF(iconstandardrel.icon = 12,1,0) AS 'brandfarlig.eps'
 FROM iconstandardrel
JOIN main ON main.code = iconstandardrel.code
JOIN iconstandard ON iconstandard.id = iconstandardrel.icon
ORDER BY iconstandardrel.code;

Which produces results like:

101577, 1, 0, 0, 0, 0, 0
101679, 0, 1, 0, 0, 0, 0
101679, 1, 0, 0, 0, 0, 0
101681, 1, 0, 0, 0, 0, 0
101748, 0, 1, 0, 0, 0, 0
101748, 1, 0, 0, 0, 0, 0

But I would like to have One line per code:

101577, 1, 0, 0, 0, 0, 0
101679, 1, 1, 0, 0, 0, 0
101681, 1, 0, 0, 0, 0, 0
101748, 1, 1, 0, 0, 0, 0

Is it possible to achieve this in pure SQL ?

-- 
Mogens Melander
+66 8701 33224


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