SQL Server :分组依据和透视

Question

我正在编写一个SQL Server查询来提取数据。

以我的查询为例：

SELECT 
    DATEPART(Year, capDateTime) Year, 
    DATEPART(Month, capDateTime) Month, 
    SUM(CAST(amount AS MONEY)) [TotalAmount]
FROM 
    dbo.iso_main
GROUP BY 
    DATEPART(Year, capDateTime), DATEPART(Month, capDateTime)
ORDER BY 
    Year, Month 

结果如下：

​

​

现在，我一直在问自己，我是如何得到这样的结果的：

|Month 7    | Month 8    |
+-----------+------------+
| 726800.00 | 2208400.00 |

我真的不关心年份，我只想让它返回几个月。

Answer 1

像这样使用PIVOT：

SELECT *
FROM (
    SELECT 
        year(capDateTime) as [year],left(datename(month,icapDateTime),3)as [month], 
        CAST(amount AS money) as Amount 
    FROM dbo.iso_main
) as s
PIVOT
(
    SUM(Amount) 
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pvt

请查看this link了解更多信息。