SQL按年份分解一列日期

Question

这可能很简单，我只是在脑子里放屁。我有一列几年的日期。如何进行查询以按年份进行分解？例如：

Code - Date       - CustName    - Sales
1001 - 2011-01-12 - Sparkies    - 50
1002 - 2012-02-11 - Spankies    - 125
1001 - 2013-01-12 - Sparkies    - 60
1003 - 2011-05-05 - Squirrelies - 75

我想展示一份报告：

Code - CustName    - 2011 - 2012 - 2013
1001 - Sparkies    - 50   - 0    - 60
1002 - Spankies    - 0    - 125  - 0
1003 - Squirrelies - 75   - 0    - 0

这在MSSQL 2008 R2中。提前感谢。。。

Answer 1

您可以使用PIVOT函数来获得结果。如果您的值数量有限，那么您将对查询进行类似以下的硬编码：

select code, custname,
  [2011], [2012], [2013]
from
(
  select code, year(date) dt,
    custname, sales
  from yourtable
) d
pivot
(
  sum(sales)
  for dt in ([2011], [2012], [2013])
) piv;

请参阅带有演示的SQL Fiddle

但是，如果您的年数未知，那么您需要考虑使用动态SQL来获得最终结果：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(year(date)) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT code, custname, ' + @cols + ' 
            from 
            (
              select code, year(date) dt,
                custname, sales
              from yourtable
            ) x
            pivot 
            (
                sum(sales)
                for dt in (' + @cols + ')
            ) p '
execute sp_executesql @query;

请参阅SQL Fiddle with Demo。这些将给出以下结果：

| CODE |    CUSTNAME |   2011 |   2012 |   2013 |
|------|-------------|--------|--------|--------|
| 1001 |    Sparkies |     50 | (null) |     60 |
| 1002 |    Spankies | (null) |    125 | (null) |
| 1003 | Squirrelies |     75 | (null) | (null) |

Answer 2

您可以使用以下内容

SELECT
    [Code],
    [CustName],
    SUM(CASE WHEN YEAR([Date]) = 2011 THEN [Sales] ELSE 0 END) AS [2011],
    SUM(CASE WHEN YEAR([Date]) = 2012 THEN [Sales] ELSE 0 END) AS [2012],
    SUM(CASE WHEN YEAR([Date]) = 2013 THEN [Sales] ELSE 0 END) AS [2013]
FROM
    [Table]
GROUP BY
    [Code],
    [CustName]

或者，如果你喜欢，可以考虑使用PIVOT。

Answer 3

SELECT CAST(YEAR(Date) AS VARCHAR(4)) AS Year, SUM(Sales) AS Sales 
FROM Orders
GROUP BY CAST(YEAR(Date) AS VARCHAR(4))
ORDER BY Year