Results 1 to 2 of 2

Thread: A solution is required

  1. #1
    Join Date
    Aug 2010
    Posts
    1

    Question 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

  2. #2
    Join Date
    Aug 2010
    Posts
    2

    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
  •