SQL查询以获得基于月份的计数

Question

这是登录表，我需要在其中显示用户名和计数，他们已经登录到应用程序，并按月分组数据，以显示基于月份明智的计数。

表名：LoginInformation

UserName   Count    Datetime
---------------------------------    
Test-1       1      2017-06-02
Test-1       1      2017-07-20
Test-1       1      2017-09-20 
Test-1       1      2017-09-20 
Test-1       1      2017-09-15
Test-1       1      2017-10-22 
Test-1       1      2017-10-23 
Test-2       1      2017-06-12 
Test-2       1      2017-06-12 
Test-2       1      2017-07-24
Test-2       1      2017-09-23 
Test-2       1      2017-09-29 
Test-2       1      2017-09-11
Test-2       1      2017-10-12 
Test-2       1      2017-10-19 
Test-2       1      2017-10-21 

我需要他的结果被显示为

UserName   Count     June July Sept Oct
----------------------------------------
Test-1       7        1    1    3    2
Test-1       7        2    1    3    3

Answer 1

;WITH ctePrepared AS (
    SELECT
       UserName
       ,TotalCount = SUM([Count]) OVER (PARTITION BY UserName)
       ,[MonthName] = DATENAME(MONTH,[Datetime])
       ,[Count]
    FROM
       @Table
)
ORDER BY
    UserName DESC

SELECT *
FROM
    ctePrepared t
    PIVOT
    (
       SUM([Count])
       FOR [MonthName] IN (January,February,March,April,May,June,July,August,September,October,November,December) 
    ) pvt

测试数据脚本(如果需要)：

DECLARE @Table AS TABLE (UserName VARCHAR(50), [Count] INT, [Datetime] DATETIME)
INSERT INTO @Table
VALUES ('Test-1',1,'2017-06-02')
,('Test-1',1,'2017-07-20')
,('Test-1',1,'2017-09-20')
,('Test-1',1,'2017-09-20')
,('Test-1',1,'2017-09-15')
,('Test-1',1,'2017-10-22')
,('Test-1',1,'2017-10-23')
,('Test-2',1,'2017-06-12')
,('Test-2',1,'2017-06-12')
,('Test-2',1,'2017-07-24')
,('Test-2',1,'2017-09-23')
,('Test-2',1,'2017-09-29')
,('Test-2',1,'2017-09-11')
,('Test-2',1,'2017-10-12')
,('Test-2',1,'2017-10-19')
,('Test-2',1,'2017-10-21')