sql - 12个月的销售，在SQL Server 2008中最多

我有一个查询要从Material wise中找出Month wise和salesdata table数据

样品

我可以找到以下数据

MaterialNo  Jan   Feb   Mar   Apr   May   Jun   Jul  Aug  Sep  Oct  Nov  Dec
1           10    15    20    30    40    45    56   85   100  95   42   32
2           12    20    86    32    45    42    62   74   86   42   84   62
3           15    20    46    54    46    42    19   0    62   75   94   98

我想要SQL SERVER 2008 R2中的最后两列(MaxSale，MaxSaleMonth)

MaterialNo  Jan   Feb   Mar   Apr   May   Jun   Jul  Aug  Sep  Oct  Nov  Dec  MaxSale   MaxSaleMonth
1           10    15    20    30    40    45    56   85   100  95   42   32   100       Sep
2           12    20    86    32    45    42    62   74   86   42   84   62   86        Mar
3           15    20    46    54    46    42    19   0    62   75   94   98   98        Dec

请建议我最简单的解决方案。

我当前的查询如下

Select
MaterialNo,
SUM(CASE WHEN CAST(SalesData.dSalesDate AS DATE) BETWEEN '01-Jan-2015' AND  '31-Jan-2015' THEN SalesData.iQty  ELSE 0 END) AS [Jan],
SUM(CASE WHEN CAST(SalesData.dSalesDate AS DATE) BETWEEN '01-Feb-2015' AND  '28-Feb-2015' THEN SalesData.iQty  ELSE 0 END) AS [Feb],
SUM(CASE WHEN CAST(SalesData.dSalesDate AS DATE) BETWEEN '01-Mar-2015' AND  '31-Mar-2015' THEN SalesData.iQty  ELSE 0 END) AS [Mar],
SUM(CASE WHEN CAST(SalesData.dSalesDate AS DATE) BETWEEN '01-Apr-2015' AND  '30-Apr-2015' THEN SalesData.iQty  ELSE 0 END) AS [Apr]
From SalesData
Group By
MaterialNo

最佳答案

使用枢轴..您可以找到最简单的方法。

select max(month), row_max = max(val)
from yourtable
pivot
(
  salesmonth
  for col in (C1, C2, C3, C4..c12)
) piv
group by id