月-YY格式枢轴

Question

现在，我有一个cte，它给出了一个长长的列表中每个销售人员每月的总销售额。我希望使用一个pivot，专门为每个月设置一个列，和销售人员列在行中，以便更容易地显示每个月的总销售额。

查询：

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

当前的输出：

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

理想情况下，我想要这个：

                 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 

清单上还有很多其他的月和销售人员，我以前从来没有做过这样的事情。查阅了几个小时的枢轴，但无法真正掌握它们的工作原理。

Answer 1

将具有原始日期的新列插入到新的临时表中

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

现在声明变量，以获得支点动态列。

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

现在做枢轴

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

• SQL小提琴