CODEWITHSUNDEEP
sql-servert-sql
user60163
user60163

Reputation: 5

Pivot with Month-YY format

Right now I've got a cte that gives me total sales per salesperson per month in a long list. I'm looking to use a pivot specifically to have a column for each month and salespeople listed in rows to more easily display the total sales under each month.

QUERY:

WITH SalesCTE (Salesperson, Date, TotalSales)
AS
(
SELECT  FirstName + ' ' + LastName AS 'SalesPerson',
        CAST(YEAR(OrderDate) AS nvarchar(10)) + '-' + DateName(MONTH,OrderDate) AS 'Date',
        SUM(TotalDue)
FROM    Person.Person p
JOIN    Sales.SalesOrderHeader s
ON      p.BusinessEntityID = s.SalesPersonID
GROUP BY FirstName, LastName, OrderDate
)


SELECT  SalesPerson, 
        Date, 
        TotalSales
FROM    SalesCTE
ORDER BY Date, SalesPerson

Output at the moment:

David Campbell  2011-December   78223.3018
Garrett Vargas  2011-December   10254.8552
Jillian Carson  2011-December   52586.674
José Saraiva    2011-December   119678.9211
Linda Mitchell  2011-December   6167.1672
Michael Blythe  2011-December   71792.8437

Ideally I want this:

                 December-11  January-12    February-12     ….
David Campbell  78223.3018  73343.3652  77431.389   
Garrett Vargas  10254.8552  176300.293  46954.6224  
Jillian Carson  52586.674   300998.1522 158997.3466 
José Saraiva    119678.9211 180684.8446 182343.3114 
Linda Mitchell  6167.1672   305145.2225 205684.0069

There are many other months and salespeople in the list and I've not done anything like this before. Looked up pivots for a few hours but can't get a hang of how they work really.

Upvotes: 0

Views: 1241

Answers (1)

Sarath Subramanian
Sarath Subramanian

Reputation: 21271

Insert the table with a new column with original date to a new temp table

SELECT *,CAST(DATES+'-01' AS DATE) ORGDATE 
INTO #NEWTABLE
FROM TEMP

Now declare variable to get columns for pivot dynamically

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + DATENAME(MONTH,ORGDATE)+'-'+RIGHT(CAST(YEAR(ORGDATE) AS
               VARCHAR(4)),2) + ']', 
              '[' + DATENAME(MONTH,ORGDATE)+'-'+RIGHT(CAST(YEAR(ORGDATE) AS VARCHAR(4)),2) + ']')
               FROM    (SELECT DISTINCT ORGDATE,[DATES] FROM #NEWTABLE) PV  
               ORDER BY ORGDATE

Now do the pivot

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 SELECT NAME, DATENAME(MONTH,ORGDATE)+''-''+RIGHT(CAST(YEAR(ORGDATE)AS VARCHAR(4)),2) logdate, value 
                 FROM #NEWTABLE
             ) x
             PIVOT 
             (
                 SUM(value)
                 FOR logdate IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

Upvotes: 1

Related Questions