How Do I Can Show Forecast Years Data From Row Into Column?

Suppose if Item-A's Sale in 2013 is 100 Quantity and I'm expecting 10% of sales growth in next year i.e. in 2014 -------------------- ITEM | YEAR | QTY | -------------------- I

Solution 1:

try this,you have to use dynamic sql

Declare@toyearint=2016Declare@forcastint=10Declare@ttable (ITEM varchar(50), years int, qty int)
insertinto@tselect'TM-A' ITEM , 2013 years, 100 qty
unionallselect'TM-B' ITEM , 2013 years, 200 qty

;with CTE1 as
(
select*from@tunionallselect b.ITEM,b.years+1,b.qty+((@forcast*b.qty)/100) from@t a 
innerjoin cte1 b on a.ITEM=b.ITEM 
and b.years<@toyear
)
    select*from
(select*from cte1 )t4
pivot(min(qty) for years in([2013],[2014],[2015],[2016]))pvt

Solution 2:

Try this:

select item, 
       qty as'2013',
       round(qty*1.1) as'2014', 
       round(qty*1.21) as'2015'from sale;

A dynamic query using stored procedure

DELIMITER $$
createprocedure p (INendINT(10))
BEGINdeclarestartint;
    declare fact FLOAT;
    SET fact =1.1;
    SELECTyearintostartFROM sale limit 1;
    SET@QUERY1= CONCAT("SELECT ITEM, QTY AS '",start,"'"); 
    WHILE start<end DO
    SETstart=start+1;
        SET@QUERY1= CONCAT(@QUERY1," ,qty*",fact," as '", start,"'");
        SET fact = fact *1.1; 
    END WHILE;
    SET@QUERY1= CONCAT(@QUERY1," from sale");
    PREPARE stmt FROM@QUERY1;
    EXECUTE stmt;
    DEALLOCATEPREPARE stmt;
END $$      

DELIMITER ;

Output:

mysql>call p(2016);
+-------+------+-------+--------+---------+| ITEM  |2013|2014|2015|2016|+-------+------+-------+--------+---------+| itemA |100|110.0|121.00|133.100|| itemB |200|220.0|242.00|266.200|+-------+------+-------+--------+---------+2rowsinset (0.00 sec)

Solution 3:

Check this question:

Pass a function return to another in the same row

Your function is simply the multiplication by 1.1