Help with MySQL Group function (I think)

[luttkens]

I have a table salary, and when I select it, it looks like this

Name          Year   Income
-------------------------------
James Smith   2008   33000
James Smith   2007   32000
James Smith   2006   31300
James Smith   2005   30100
James Smith   2004   28000
Oliver M.     2008   19700
Oliver M.     2006   18000
Oliver M.     2005   16000

How can I make the result look like this instead? I guess there some kind of group function included in the answer...

Name          2008  2007  2006  2005  2004  
---------------------------------------------
James Smith   33000 32000 31300 30100 28000
Oliver M.     19700 null  18000 16000 null

[coolsti]

You are asking MySQL to transform rows into columns. I suppose you could do this with a query but it would be a little complicated, and probably needs to know in advance that the columns you wish to show are for the 5 years that you are showing. In other words, it would maybe not be generalized to another set of years.

Usually, to do a presentation like this, you would take the results out of MySQL in a table format like what you have now, and use a scripting or other language to place the results into the column form that you want.

What may help you here is the group_concat function with a group by clause.

[moltendorf]

It's a fairly complicated query, but here is how you would do it.

Simply Replace `helper` with the name of your table.

SET @y1 = YEAR(CURDATE());
SET @y2 = (YEAR(CURDATE()) - (1));
SET @y3 = (YEAR(CURDATE()) - (2));
SET @y4 = (YEAR(CURDATE()) - (3));
SET @y5 = (YEAR(CURDATE()) - (4));
 
CREATE TEMPORARY TABLE `temporary`
    (
        `name` VARCHAR(255) NOT NULL,
        `1` BIGINT(12) UNSIGNED,
        `2` BIGINT(12) UNSIGNED,
        `3` BIGINT(12) UNSIGNED,
        `4` BIGINT(12) UNSIGNED,
        `5` BIGINT(12) UNSIGNED
    )
    ENGINE = MEMORY;
 
INSERT INTO `temporary`
    (
        `name`
    )
    SELECT DISTINCT `name`
        FROM `helper`;
 
UPDATE `temporary`
    SET
        `temporary`.`1` =
            (
                SELECT `helper`.`income`
                    FROM `helper`
                    WHERE 
                            `helper`.`year` = @y1
                        AND `helper`.`name` = `temporary`.`name`
            ),
        `temporary`.`2` =
            (
                SELECT `helper`.`income`
                    FROM `helper`
                    WHERE 
                            `helper`.`year` = @y2
                        AND `helper`.`name` = `temporary`.`name`
            ),
        `temporary`.`3` =
            (
                SELECT `helper`.`income`
                    FROM `helper`
                    WHERE 
                            `helper`.`year` = @y3
                        AND `helper`.`name` = `temporary`.`name`
            ),
        `temporary`.`4` =
            (
                SELECT `helper`.`income`
                    FROM `helper`
                    WHERE 
                            `helper`.`year` = @y4
                        AND `helper`.`name` = `temporary`.`name`
            ),
        `temporary`.`5` =
            (
                SELECT `helper`.`income`
                    FROM `helper`
                    WHERE 
                            `helper`.`year` = @y5
                        AND `helper`.`name` = `temporary`.`name`
            );
 
SELECT * FROM `temporary`;

Please note that this query only selects results from the past 5 years.
To do more then that, you'd have to adapt it a bit.

I have no clue to make it into a dynamic form as of right now.
-You'd probably need some advanced loops to be used.

The result table is:

name        1     2     3     4     5
-----------------------------------------
James Smith 33000 32000 31300 30100 28000
Oliver M.   19700 NULL  18000 16000 NULL

[Atli]

This can be done with MySQL (like shown in the previous post), but I wouldn't advice it.
You would be far better of using some scripting language or some other API to format the data.

MySQL is a database server, meant to store data, not re-arrange it in pretty formations. That is the job of an API.

How would you be executing this query?
Is this meant to be a part of some application?
Would it not be possible to do this using the language in which that application is written?

[luttkens]

This can be done with MySQL (like shown in the previous post), but I wouldn't advice it.
You would be far better of using some scripting language or some other API to format the data.

MySQL is a database server, meant to store data, not re-arrange it in pretty formations. That is the job of an API.

How would you be executing this query?
Is this meant to be a part of some application?
Would it not be possible to do this using the language in which that application is written?

Thanks for your answers!

Well I need it to be dynamic so I used the group_concat function and combined it with a PHP-script.

It works very nice!