Group table records by Item than Year I have a SQL table that is layed out as follows:

{Itemcode}  {FiscalYear}    {FiscalMonth}   {StartingQty}   {PeriodChangeQty}

Ball    2013    01  15000   -7500

Ball    2013    02  0   1000

Ball    2013    03  0   -500

Ball    2014    01  8000    -1000


I want to generate a table that looks like this

{ItemCode}  {FiscalYear}    {01}    {02}    {03}    {04}    {05}    {06}    {07}    {08}    {09}    {10}    {11}    {12}

Ball    2013    7500    8500    8000                                    

Ball    2014    7000                                            


Not sure at all how to do this.  I can get close with a pivot table but not quite.  Any ideas or clues as to how to accomplish this?
If you're on SQL Server 2012 or 2014, this is an aggregate that you can calculate with an ordered window function.

SELECT ItemCode, FiscalYear, FiscalMonth,
       SUM(StartingQty+PeriodChangeQty) OVER (
           PARTITION BY ItemCode, FiscalYear
           ORDER BY FiscalMonth
           ROWS UNBOUNDED PRECEDING) AS EndingQty
FROM dbo.theTable;


PARTITION BY defines a partition that spans over all of the rows for the current ItemCode and FiscalYear. Within this partition, you define a window using "rows unbounded preceding" that spans from the first FiscalMonth up to and including the current FiscalMonth. Those are the rows for which you SUM() the starting quantity and the period change.

All of this assumes that the starting quantity is given for the first month of each year and zero for all other months.

Now, all you have to do is apply a PIVOT to this in order to get the output in neat columns.

If you're on SQL Server 2008 R2 or Azure SQL Database, ordered window functions aren't supported. You'll have to resort to a much less pretty join-based solution, which also comes with a potentially hefty performance penalty. Something like:

SELECT a.ItemCode, a.FiscalYear, a.FiscalMonth,
       SUM(b.StartingQty+b.PeriodChangeQty) AS EndingQty
FROM dbo.theTable AS a
INNER JOIN dbo.theTable AS b ON
    a.ItemCode=b.ItemCode AND
    a.FiscalYear=b.FiscalYear AND
    a.FiscalMonth&gt;=b.FiscalMonth
GROUP BY a.ItemCode, a.FiscalYear, a.FiscalMonth;


Note: This query assumes that (ItemCode, FiscalYear, FiscalMonth) is unique.

I haven't tested these queries, so they may contain spelling (or thinking) errors. Best of luck.