A solution is required

Given Below is a Table let us suppose emp.

ID Year Grade
1 2000 1
2 2000 2
3 2000 3
1 2001 2
2 2001 3
3 2001 1
1 2002 3
2 2002 1
3 2002 2
NULL NULL NULL

I want to write a query which gives me the output as

ID Grade(2000) Grade(2001) Grade(2002)

1 1 2 3
2 2 3 1
3 3 1 2


also kindly try to provide me a generic solution so that if the number of years increases for an id it gives that number of columns.

Thanks
Praveen

Step -1 :
Assumed that there is a table called emp with following data :

ID Yr Grade
1 2000 1
2 2000 2
3 2000 3
1 2001 2
2 2001 3
3 2001 1
1 2002 3
2 2002 1
3 2002 2


Step -2 :
Here is the generic solution:

DELIMITER $$

DROP PROCEDURE IF EXISTS `cm`.`try1` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `try1`()
BEGIN

Declare done INT default 0;
DECLARE _ID INT DEFAULT 0;
DECLARE _CN VARCHAR(15) DEFAULT '';
DECLARE _YR INT DEFAULT 0;
DECLARE _GRD INT DEFAULT 0;

DECLARE cur1 CURSOR FOR SELECT distinct yr FROM emp;
DECLARE cur2 CURSOR FOR SELECT SUBSTRING(Column_Name,6),Column_Name FROM information_schema.`COLUMNS`
where table_name='tbl_Report' and SUBSTRING(Column_Name,6)!=' ';



DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;


DROP TABLE IF EXISTS tbl_Report;
CREATE TABLE tbl_Report AS SELECT distinct ID from emp;

REPEAT
FETCH cur1 INTO _YR;
IF NOT done THEN

SET @stmt_expr=CONCAT('ALTER TABLE tbl_Report ADD COLUMN Grade',_YR,' INT DEFAULT 0;');
PREPARE stmt FROM @stmt_expr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END IF;
UNTIL done END REPEAT;
CLOSE cur1;



-- AS THE TABLE IS CREATED , LETS UPDATE THE DATA
SET done = 0;

OPEN cur2;

REPEAT
FETCH cur2 INTO _YR, _CN;
IF NOT done THEN
SET @stmt_expr=CONCAT('update tbl_Report set ',_CN,'= (select Grade from emp where tbl_Report.id = emp.id and emp.yr =',_YR,');');
PREPARE stmt FROM @stmt_expr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur2;

select * from tbl_Report;

END $$

DELIMITER ;

*******************************************

Step -3 :
Execute the above procedure as Call Try1(); , you will get the
desired result as shown below

ID Grade2000 Grade2001 Grade2002
1 1 2 3
2 2 3 1
3 3 1 2

hope this suffices .