Is it possible to make a query like this?

Question

I have a table like this on mySQL db:

Olimpiade	Sport	Disciplina	Categoria
London 2012	Athletics	100m	men
London 2012	Athletics	100m	woman
Beijing 2008	Athletics	200m	men
Beijing 2008	Athletics	200m	women
Athens 2004	Athletics	800m	men
Athens 2004	Athletics	800m	women

and so on. I don't know if I could set up a query like this, but what I would like to get is a result as follow:

Disciplina	Categoria	London 2012	Beijing 2008	Athens 2004
100m	men	yes	no	yes
100m	women	yes	yes	no
200m	men	yes	no	yes
200m	women	yes	yes	no
800m	men	yes	yes	yes
800m	women	yes	yes	yes

that is if the trial is present or not for that olympics edition.

belongs to [pivot table](http://www.artfulsoftware.com/infotree/qrytip.php?id=78) problem. — 1000111, Feb 07 '16 at 12:08
Have a look at this [post](http://stackoverflow.com/questions/7674786/mysql-pivot-table). Then try yourself first. — 1000111, Feb 07 '16 at 12:09

digitai · Answer 1 · 2016-02-07T15:03:54.567

3

Yes you can, use CASE like this,

Select Disciplina, Categoria, 
CASE when Olimpiade = 'London 2012' then 'yes' else 'no' end as 'London 2012', 
CASE when Olimpiade = 'Beijing 2008' then 'yes' else 'no' end as 'Biejing 2008',
CASE when Olimpiade = 'Athens 2004' then 'yes' else 'no' end as 'Athens 2004'    from tableName group by Disciplina, Categoria order by Disciplina, Categoria

edited Feb 07 '16 at 15:03

answered Feb 07 '16 at 12:14

digitai

1,870
2
20
37

1

Ok, but it's strange because I get 'yes' only on first (London 2012) and the others 'no', even if I have to get 'yes'. It seems it works only for the first case – Idro Feb 07 '16 at 15:10

Henrik · Answer 2 · 2016-02-07T16:43:40.800

You can build a query with an inner select. The inner select prepares data by adding the columns. The outer select groups the data:

SELECT
    Disciplina,
    Categoria,
    IF (MAX(`London 2012`) > 0, 'yes', 'no') AS 'London 2012',
    IF (MAX(`Beijing 2008`) > 0, 'yes', 'no') AS 'Beijing 2008',
    IF (MAX(`Athens 2004`) > 0, 'yes', 'no') AS 'Athens 2004'
FROM
    (
        SELECT 
            Disciplina, 
            Categoria, 
            IF (Olimpiade = 'London 2012', 1, 0) AS 'London 2012',
            IF (Olimpiade = 'Beijing 2008', 1, 0) AS 'Beijing 2008',
            IF (Olimpiade = 'Athens 2004', 1, 0) AS 'Athens 2004'
        FROM YourTableName
    ) AS Games
GROUP BY Disciplina, Categoria
ORDER BY Disciplina, Categoria

You have to replace YourTableName with the name of your table.

Is it possible to make a query like this?

2 Answers2