Arranging Data by Month/Year with t-SQL (Grouping Items)

Question

I am using the following SQL Statement to summarize my sales history data by Qty sold for each month.

WITH StockSales AS
(
    SELECT
        CASE
            WHEN Month(PostST.TxDate) = 1 THEN 'Jan'
            WHEN Month(PostST.TxDate) = 2 THEN 'Feb'
            WHEN Month(PostST.TxDate) = 3 THEN 'Mar'
            WHEN Month(PostST.TxDate) = 4 THEN 'Apr'
            WHEN Month(PostST.TxDate) = 5 THEN 'May'
            WHEN Month(PostST.TxDate) = 6 THEN 'Jun'
            WHEN Month(PostST.TxDate) = 7 THEN 'Jul'
            WHEN Month(PostST.TxDate) = 8 THEN 'Aug'
            WHEN Month(PostST.TxDate) = 9 THEN 'Sept'
            WHEN Month(PostST.TxDate) = 10 THEN 'Oct'
            WHEN Month(PostST.TxDate) = 11 THEN 'Nov'
            WHEN Month(PostST.TxDate) = 12 THEN 'Dec'
        END AS MonthSold, 
        YEAR(PostST.TxDate) AS YearSold, 
        CONCAT(StkItem.Description_1, ' - ', StkItem.Code) AS Item, 
        CASE
            WHEN PostST.TrCodeID = 30  THEN PostST.Quantity * -1
            WHEN PostST.TrCodeID = 34 THEN PostST.Quantity * 1
            ELSE 0
        END AS QtySold
    FROM 
        StkItem
    INNER JOIN 
        PostST ON PostST.AccountLink = StkItem.StockLink
    WHERE 
        PostST.TrCodeID IN (34, 30)
)
SELECT
    StockSales.MonthSold, 
    StockSales.YearSold, 
    StockSales.Item, 
    SUM (StockSales.QtySold) AS QtySold
FROM 
    StockSales
GROUP BY 
    StockSales.QtySold, StockSales.MonthSold, StockSales.YearSold, StockSales.Item

I get the following format:

However, what I want is the Item to only appear once (grouped) and then the each month and year to be a separate field with the SUM of QtySold under each month.

As an example, I have just set it up in Excel how I want it to look:

Is there a way of setting up the field differently?

Thank you :)

But seriously, what you are seeking is known as a "pivot", however to achieve column names that vary by date usually also requires "dynamic sql" — Paul Maxwell, Aug 28 '15 at 08:20
see: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Paul Maxwell, Aug 28 '15 at 08:24
Images dont open in selected office networks request you to please provide samples — Murtaza, Aug 28 '15 at 10:36

xan · Answer 1 · 2015-08-28T08:26:57.630

You need to "Pivot" the data to get the results in the format you want.

Excel

One way is to import the data into Excel, where you can create a Pivot Table from the data, to build the structure you want. The quick way to do this is:

Paste in the data (with headings) from SSMS
Select the data
Click "Format as Table" in excel, ticking "Headers in 1st row"
When the table is selected, a new ribbon tab appears. In there there's a button called "Summarise with Pivot Table"

Once the pivot table is created, you can then select Item on the rows axis, year and month on the columns and # sold as values.

SSRS

If you have SQL Server Reporting services available, you can also pivot the table via a Tablix in a SSRS report.

Create a dataset based on your query (it's always best to stick SSRS queries into a stored procedure so that the query is "visible" at the SQL Server level and will show up as a dependency when making schema changes).
For convenience, add a column which is the combination of year and month in your preferred format for display.
Add a matrix to the report
Set "Item" as the row value
Set your "Date" field as the column grouping.
Set the #items sold as the value at the details level.

SQL Server Another option is the PIVOT SQL command, see here https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx.

It's not something I've done often as it's only really useful when you know the exact set of column headings you want (the pivoted fields) at the time you write the query, since you still need to specify all the columns in the query.

For example, if you've got a fixed data set like a static set of currencies for example it works well.

Where you have dynamic data (like in your case, a set of year/months which might change) it's not so useful, but might be OK if you have a fixed reporting period.

score 0 · Answer 2 · answered Aug 28 '15 at 10:35

First of all you need to convert your months in desired format, you can do that by using belwo script within cte:

SELECT
    CASE
        WHEN Month(PostST.TxDate) = 1 THEN 'Jan'
        WHEN Month(PostST.TxDate) = 2 THEN 'Feb'
       .
       .
       .
        WHEN Month(PostST.TxDate) = 12 THEN 'Dec'
    END) + CAST ( YEAR(PostST.TxDate) AS VARCHAR) AS MonthSold, 
       .

Then use SQL pivot for pivoting the data:

SELECT * FROM ( SELECT MonthSold, YearSold, Item, QtySold
FROM 
StockSales ) as data1
PIVOT ( SUM( QtySold ) FOR MonthSold IN ( 'Jan-13', 'Feb-13', 'Mar-13', 'Jan-14', 'Feb-14', 'Jan-15') ) AS PivotData

You will have to manually put all the monthSold values in FOR MonthSold IN ( ) values. There is another way, dynamic sql, that lets you handle it automatically. You can try this script, and look for that method. Hope it helps

score -1 · Answer 3 · edited Nov 30 '16 at 17:53

USE CTE with Pivot table

WITH Sales_CTE (Employee_No, [Employee Name], MonthSold,Amount)
AS
-- Define the CTE query.
(
   SELECT  EMP.Employee_No,Emp.First_Name+' '+Emp.Last_Name [Employee Name],   


       CASE
        WHEN Month(SLC.Post_Date) = 1 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Jan'
        WHEN Month(SLC.Post_Date) = 2 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Feb' 
        WHEN Month(SLC.Post_Date) = 3 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Mar' 
        WHEN Month(SLC.Post_Date) = 4 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Apr' 
        WHEN Month(SLC.Post_Date) = 5 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-May' 
        WHEN Month(SLC.Post_Date) = 6 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Jun' 
        WHEN Month(SLC.Post_Date) = 7 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Jul' 
        WHEN Month(SLC.Post_Date) = 8 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Aug' 
        WHEN Month(SLC.Post_Date) = 9 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Sep' 
        WHEN Month(SLC.Post_Date) = 10 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Oct' 
        WHEN Month(SLC.Post_Date) = 11 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Nov'        
        WHEN Month(SLC.Post_Date) = 12 THEN CAST (right(YEAR(SLC.Post_Date),2) AS VARCHAR)+'-Dec'
    END  AS [MonthSold], 
  CASE WHEN SLC.Transaction_Type IN ('B') 
                      THEN SLC.Commission_Received_Amount ELSE SLC.Commission_Amount + IsNULL(SLC.Pleasant_ChargeBack_Amount, 0) 
                      + IsNULL(SLC.Preferred_ChargeBack_Amount, 0) + IsNULL(SLC.Non_Preferred_ChargeBack_Amount, 0) + IsNULL(SLC.Other_ChargeBack_Amount, 0) 
                      END AS  [Amount] 
    FROM         tbl_Sales SLC JOIN
                      tbl_Employee EMP ON (EMP.Employee_ID = SLC.Employee_ID AND EMP.Load_Status = '0' AND SLC.Invoice_Status <> 'V') LEFT OUTER JOIN
                      tbl_Preferred PRV ON (PRV.Segment_Type = SLC.Segment_Type AND PRV.Club_Code = SLC.Club_Code AND PRV.Vendor_Code = SLC.Vendor_Code AND 
                      PRV.Effective_Date <= SLC.Post_Date AND PRV.Expiration_Date >= SLC.Post_Date) WHERE SLC.Post_Date>='2015-01-01'
)

SELECT *
FROM (
   SELECT * FROM Sales_CTE
) as s  
PIVOT
(
    SUM(Amount)
    FOR [MonthSold] IN ("15-Jan","15-Feb","15-Mar","15-Apr","15-May","15-Jun","15-Jul","15-Aug","15-Sep","15-Oct","15-Nov","15-Dec","16-Jan","16-Feb","16-Mar","16-Apr","16-May","16-Jun","16-Jul","16-Aug","16-Sep","16-Oct","16-Nov","16-Dec")
)AS pvt  order by Employee_No asc

GO

Thank you. I will try this tomorrow. :) – Gray Meiring Nov 30 '16 at 19:20 — Gray Meiring, Nov 30 '16 at 19:20

Arranging Data by Month/Year with t-SQL (Grouping Items)

3 Answers3