SQL monthly sums in one row

[MikeMV]

I am using VFP 9.0 SP2 with data from a MS SQL 2000 server. I need to come up with monthly sales qtys per item per month for the current year. I have code that will give me a row per stockcode per month, but I would like to see if I can return a single row per stockcode with a column for every month within the row. I am wondering if this can be done. The code I have is:

CREATE SQL VIEW vsalesmonth;
REMOTE CONNECTION SysProData;
AS SELECT InvMovements.StockCode, SUM(InvMovements.TrnQty) AS TrnQty, InvMovements.TrnMonth FROM InvMovements;
WHERE InvMovements.Warehouse='A' AND InvMovements.TrnYear=(Year (current_timestamp)) AND InvMovements.MovementType <> 'I';
GROUP BY InvMovements.TrnMonth, InvMovements.StockCode;
ORDER BY InvMovements.TrnMonth, InvMovements.StockCode
*
CREATEOFFLINE("vsalesmonth")

What I am gettin now is:

stockcode trnqty trnmonth

12345 100 1
12345 105 2
23456 130 1
23456 150 2

I would like to extract:

stockcode trnqty1 trnqty2
12345 100 105
23456 130 150

I will greatly appreciate any feedback you can provide.

 

[bborissov]

You need so called PIVOT-ing.
Try this:

CREATE SQL VIEW vsalesmonth;
REMOTE CONNECTION SysProData;
AS;
SELECT InvMovements.StockCode,
       SUM(CASE WHEN InvMovements.TrnMonth = 1
                     THEN InvMovements.TrnQty
                ELSE 0 END) AS TrnQty1,
       SUM(CASE WHEN InvMovements.TrnMonth = 2
                     THEN InvMovements.TrnQty
                ELSE 0 END) AS TrnQty2, 
...
       SUM(CASE WHEN InvMovements.TrnMonth = 12
                     THEN InvMovements.TrnQty
                ELSE 0 END) AS TrnQty12
FROM InvMovements
WHERE InvMovements.Warehouse='A' AND
      InvMovements.TrnYear=(Year (current_timestamp)) AND
      InvMovements.MovementType <> 'I'
GROUP BY InvMovements.TrnMonth, InvMovements.StockCode
ORDER BY InvMovements.TrnMonth, InvMovements.StockCode

and see if this is what you want. If it is just try to convert it to RV.
Sorry if I have any syntax errors, but I skip using views since CursorAdapters come to VFP.
Just try that with QA (sql server side):

SELECT InvMovements.StockCode,;
       SUM(CASE WHEN InvMovements.TrnMonth = 1;
                     THEN InvMovements.TrnQty
                ELSE 0 END) AS TrnQty1,;
       SUM(CASE WHEN InvMovements.TrnMonth = 2;
                     THEN InvMovements.TrnQty
                ELSE 0 END) AS TrnQty2, 
...
       SUM(CASE WHEN InvMovements.TrnMonth = 12;
                     THEN InvMovements.TrnQty
                ELSE 0 END) AS TrnQty12;
FROM InvMovements;
WHERE InvMovements.Warehouse='A' AND;
      InvMovements.TrnYear=(Year (current_timestamp)) AND;
      InvMovements.MovementType <> 'I';
GROUP BY InvMovements.TrnMonth, InvMovements.StockCode;
ORDER BY InvMovements.TrnMonth, InvMovements.StockCode


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MikeMV]

Thank you very much Borislav,

That worked great, the only thing I had to do was remove the grouping by trnmonth, otherwise I would still get 12 records per each stockcode.