CODEWITHSUNDEEP
mysqlsqlpivot
Idro
Idro

Reputation: 349

Is it possible to make a query like this?

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.

Upvotes: 7

Views: 161

Answers (2)

Henrik
Henrik

Reputation: 2921

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.

Upvotes: 3

digitai
digitai

Reputation: 1842

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

Upvotes: 3

Related Questions