From: Jan Steinman
Date: November 19 2012 12:49pm
Subject: Re: Dynamic crosstab got me lost.
List-Archive: http://lists.mysql.com/mysql/228660
Message-Id: <0F8DFE49-999E-4BF4-B4F9-1B9EB8ECCDDF@Bytesmiths.com>
MIME-Version: 1.0 (Apple Message framework v1085)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

> From: "Mogens Melander" <mogens@stripped>
>=20
> So, I got a little further with my problem. I found an article
> on:
>=20
> =
http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-cr=
osstab
>=20
> Describing how to do the dynamic generation of SQL statements. That's
> all good, kind of. The resulting SQL looks like this:
>=20
> SELECT main.code
> , IF(iconstandardrel.icon =3D 4,1,0) AS 'internationalt_produkt.eps'
> , IF(iconstandardrel.icon =3D 3,1,0) AS 'god_vaerdi.eps'
> , IF(iconstandardrel.icon =3D 2,1,0) AS 'for_miljoeets_skyld.eps'
> , IF(iconstandardrel.icon =3D 1,1,0) AS 'ergonomisk_produkt.eps'
> , IF(iconstandardrel.icon =3D 6,1,0) AS 'saml_selv.eps'
> , IF(iconstandardrel.icon =3D 12,1,0) AS 'brandfarlig.eps'
> FROM iconstandardrel
> JOIN main ON main.code =3D iconstandardrel.code
> JOIN iconstandard ON iconstandard.id =3D iconstandardrel.icon
> ORDER BY iconstandardrel.code;
>=20
> Which produces results like:
>=20
> 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
>=20
> But I would like to have One line per code:
>=20
> 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
>=20
> Is it possible to achieve this in pure SQL ?

I think you need GROUP BY main.code.

----------------
:::: If you worry about what might be, and wonder what might have been, =
you will ignore what is. -- Robert Anthony
:::: Jan Steinman, EcoReality Co-op ::::