List:General Discussion« Previous MessageNext Message »
From:hsv Date:November 19 2012 5:23pm
Subject:Re: Dynamic crosstab got me lost.
View as plain text  
>>>> 2012/11/19 04:49 -0800, Jan Steinman >>>>
> 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 ?

I think you need GROUP BY main.code.
<<<<<<<<
Yes, with fit aggregate functions around the IFs: MAX would work, and also BIT_OR. I hav
seen no aggregate function that corresponds to OR, the most natural in your case.

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