-
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
-
Re: Solution As Required
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 .
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|