hi,
thank you for reply and code ,
for you example(table) it works, but for my table i see duplicated stk_code,
i compare between fields type , same thing... i try via date as britsh or YDM same result.
can i sent small table as 10 to 15 records.
>>hi,
>>i need to sum as
>>
>>stk_code m_1 m_2.... m_12
>>120004 4521 65214 569874
>>1350002 657 6744 88875
>
>
>I don't understand your idea,
>but may be
>
>CREATE TABLE Test (stk_code C(10), trn_type N(2),trn_date D , cst_prce N(10,2),str_nmbr C(10),trn_qnty N(10,2))
>INSERT INTO test VALUES("11" , 1 , {^2006/01/03}, 12 ,"12" , 15.67)
>INSERT INTO test VALUES("11" , 1 , {^2006/01/31}, 12 ,"12" , 10.67)
>
>INSERT INTO test VALUES("11" , 1 , {^2006/02/03}, 12 ,"12" , 1.00)
>INSERT INTO test VALUES("11" , 1 , {^2006/02/05}, 12 ,"12" , 2.34)
>INSERT INTO test VALUES("11" , 1 , {^2006/02/27}, 12 ,"12" , 3.04)
>
>INSERT INTO test VALUES("11" , 1 , {^2006/10/05}, 12 ,"12" , 1.00)
>INSERT INTO test VALUES("11" , 1 , {^2006/10/08}, 12 ,"12" , 2.34)
>INSERT INTO test VALUES("11" , 1 , {^2006/10/31}, 12 ,"12" , 3.04)
>
>INSERT INTO test VALUES("11" , 2 , {^2006/03/03}, 12 ,"12" , 15.67)
>INSERT INTO test VALUES("11" , 2 , {^2006/03/31}, 12 ,"12" , 10.67)
>
>INSERT INTO test VALUES("11" , 2 , {^2006/09/03}, 12 ,"12" , 1.00)
>INSERT INTO test VALUES("11" , 2 , {^2006/09/05}, 12 ,"12" , 2.34)
>INSERT INTO test VALUES("11" , 2 , {^2006/09/27}, 12 ,"12" , 3.04)
>
>INSERT INTO test VALUES("11" , 2 , {^2006/12/05}, 12 ,"12" , 5.33)
>INSERT INTO test VALUES("11" , 2 , {^2006/12/08}, 12 ,"12" , 2.31)
>INSERT INTO test VALUES("11" , 2 , {^2006/12/31}, 12 ,"12" , 3.14)
>
>SELECT stk_code,trn_type,cst_prce,str_nmbr, ;
> SUM(IIF(MONTH(trn_date)=1 , trn_qnty, 000000.00)) AS m1_t ,;
> SUM(IIF(MONTH(trn_date)=2 , trn_qnty, 000000.00)) AS m2_t ,;
> SUM(IIF(MONTH(trn_date)=3 , trn_qnty, 000000.00)) AS m3_t ,;
> SUM(IIF(MONTH(trn_date)=4 , trn_qnty, 000000.00)) AS m4_t ,;
> SUM(IIF(MONTH(trn_date)=5 , trn_qnty, 000000.00)) AS m5_t ,;
> SUM(IIF(MONTH(trn_date)=6 , trn_qnty, 000000.00)) AS m6_t ,;
> SUM(IIF(MONTH(trn_date)=7 , trn_qnty, 000000.00)) AS m7_t ,;
> SUM(IIF(MONTH(trn_date)=8 , trn_qnty, 000000.00)) AS m8_t ,;
> SUM(IIF(MONTH(trn_date)=9 , trn_qnty, 000000.00)) AS m9_t ,;
> SUM(IIF(MONTH(trn_date)=10 , trn_qnty, 000000.00)) AS m10_t,;
> SUM(IIF(MONTH(trn_date)=11 , trn_qnty, 000000.00)) AS m11_t,;
> SUM(IIF(MONTH(trn_date)=12 , trn_qnty, 000000.00)) AS m12_t ;
> FROM test ;
> WHERE YEAR(trn_date) = 2006;
> GROUP BY stk_code, trn_type, cst_prce, str_nmbr;
> INTO CURSOR MyCursor
>
> BROWSE NORMAL
>
>
