Re: Dynamic crosstab got me lost.

[Shawn Green]

On 11/19/2012 9:02 AM, Mogens Melander wrote:

On Mon, November 19, 2012 13:49, Jan Steinman wrote:

From: "Mogens Melander" <mog...@fumlersoft.dk>

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 ?

I think you need GROUP BY main.code.

No, that ain't it either. I've tried that.

But thanks anyway :)

I can't figure out what to call this operation, to do a search.
Someone out there must have done this before.

You need both GROUP BY and either SUM or MAX, like this

SELECT
...
, SUM(IF(iconstandardrel.icon = 3,1,0)) AS 'god_vaerdi.eps'
...
GROUP BY main.code;


or

SELECT
...
, MAX(IF(iconstandardrel.icon = 3,1,0)) AS 'god_vaerdi.eps'
...
GROUP BY main.code;

That will combine (aggregate) your rows together.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql