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,792
2
18
35

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