数据透视表日期 &年度明智的数据显示 sql server 2005 [英] PIVOT Table Date & Year wise data display sql server 2005

问题描述

这是我的查询

SELECT *
FROM (SELECT CurDate, YEAR(CurDate) AS orderyear, Warranty_Info
      FROM eod_main where year(CurDate)>=2009 and year(CurDate)<=2011) AS D
  PIVOT(SUM(Warranty_Info) FOR orderyear IN([2009],[2010],[2011])) AS P

上述查询返回数据，但CurDate返回日期是返回同月的多个日期.

the above query return data but CurDate return date it is is return multiple date for same month.

我希望 SUM(Warranty_Info) 每个月和每年只返回一次

i want that SUM(Warranty_Info) should return only once for every month and year

输出应该是这样的

Month     2009     2010    2011    2012   2013
-----     ----     ----    ----    ----   -----
1          10       0       11      32      98
2          20       10      21      11      44
3          0        224     33      77      31

我的查询中存在某种问题，这就是为什么它返回同一个月的多个数据，例如

some kind of problem is there in my query and that is why it is returning multiple data for same month like

请帮助我进行正确的查询.谢谢

please help me to have the right query. thanks

推荐答案

不要使用 CurDate 作为包含年、月和日的完整日期，我建议使用 Month() 函数返回每个月的数值.这将允许您按月而不是完整日期进行分组:

Instead of using CurDate as the full date with year, month and day, I would suggest using the Month() function to return the numeric value of each month. This will allow you to group by month instead of the full date:

SELECT dateMonth, [2009],[2010],[2011]
FROM 
(
  SELECT month(CurDate) dateMonth, 
    YEAR(CurDate) AS orderyear, Warranty_Info
  FROM eod_main 
  where year(CurDate)>=2009 
    and year(CurDate)<=2011
) AS D
PIVOT
(
  SUM(Warranty_Info) 
  FOR orderyear IN([2009],[2010],[2011])
) AS P;

参见SQL Fiddle with Demo

这篇关于数据透视表日期 &年度明智的数据显示 sql server 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！