Hi mika,
thnx for suggestion, but i have to do it in that way bcoz of such criteria, and i got the solution through the following code using Pivoting....
CREATE PROCEDURE `table_name`(
PYear_1 VARCHAR(50),
PYear_2 VARCHAR(50),
PYear_3 VARCHAR(50),
PYear_4 VARCHAR(50),
PYear_5 VARCHAR(50),
PYear_6 VARCHAR(50),
PYear_7 VARCHAR(50),
PYear_8 VARCHAR(50)
)
BEGIN
SET @NYear_1 =PYear_1;
SET @NYear_2 =PYear_2;
SET @NYear_3 =PYear_3;
SET @NYear_4 =PYear_4;
SET @NYear_5 =PYear_5;
SET @NYear_6 =PYear_6;
SET @NYear_7 =PYear_7;
SET @NYear_8 =PYear_8;
SET @s = CONCAT("SELECT id,
SUM( IF( Year_1 = ", @NYear_1,", Year_1_Value, 0 ) ) AS '", @NYear_1,"',
SUM( IF( Year_2 = ", @NYear_2,", Year_2_Value, 0 ) ) AS '", @NYear_2,"',
SUM( IF( Year_3 = ", @NYear_3,", Year_3_Value, 0 ) ) AS '", @NYear_3,"',
SUM( IF( Year_4 = ", @NYear_4,", Year_4_Value, 0 ) ) AS '", @NYear_4,"',
SUM( IF( Year_5 = ", @NYear_5,", Year_5_Value, 0 ) ) AS '", @NYear_5,"',
SUM( IF( Year_6 = ", @NYear_6,", Year_6_Value, 0 ) ) AS '", @NYear_6,"',
SUM( IF( Year_7 = ", @NYear_7,", Year_7_Value, 0 ) ) AS '", @NYear_7,"',
SUM( IF( Year_8 = ", @NYear_8,", Year_8_Value, 0 ) ) AS '", @NYear_8,"'
FROM table where tableid=19 GROUP BY id");
PREPARE stmt FROM @s;
EXECUTE stmt;
END$$