Question

我有一个 SQL 查询, 以以下格式提供数据 ;

Total Hours   Year   
  100.00      2012 
  200.00      2012 
  300.00      2012 
   75.00      2011 
  150.00      2011 
   50.00      2010 
  125.00      2010

我总时数总和并带来结果设定为:

2012   2011  2010
 600    225   175

帮我一下,如果需要更多信息,请通知我

Answer 1

您可以使用 PIVOT 执行此操作, 要么是静态 PIVOT, 硬代码为年数, 要么是动态 PIVOT, 用来创建运行查询的年份列表 :

静态脉搏:

create table table1
(
  totalhours decimal(10, 2),
  year int
)

insert into table1 values(100, 2012)
insert into table1 values(200, 2012)
insert into table1 values(300, 2012)
insert into table1 values(75, 2011)
insert into table1 values(150, 2011)
insert into table1 values(50, 2010)
insert into table1 values(125, 2010)

select *
from
(
  select *
  from table1
) x
pivot
(
  sum(totalhours)
  for year in ([2012], [2011], [2010])
) p

< a href=" "http://sqlfiddle.com/#! 3/b2fe2/2/1, rel=" noreferrer" > 这里有一个 SQL 中小插曲

动态枢纽 :

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct  ,  + QUOTENAME(c.year) 
            FROM table1 c
            FOR XML PATH(  ), TYPE
            ).value( . ,  NVARCHAR(MAX) ) 
        ,1,1,  )

set @query =  SELECT   + @cols +   from 
            (
                select totalhours, year
                from table1
           ) x
            pivot 
            (
                 sum(totalhours)
                for year in (  + @cols +  )
            ) p  


execute(@query)

两者的结果都一样

Answer 2

在 msdn < a href=" http://msdn.microsoft.com/ en- us/library/ms 177410%28v=sql. 105%29.aspx" rel=“nofollow” >Using PIVOT和UNPIVOT 上做这个红字,这将很容易解决问题。

SQL SERVER - PIVOT和UNPIVOT 表格示例

Answer 3

您可以使用 sql grouping http://www.w3schools.com/sql/sql_groupby.asp

select 
    [Year], 
    SUM([Hours]) as HoursByYear
from 
    #table  
group by 
    [Year]

结果:

    Year    HoursByYear
    2010    175
    2011    225
    2012    600

或“强度”或“强度”或“强度”,如Pranay Rana建议

select
    [2010], [2011], [2012]
from
    (select [YEAR], [Hours]
        from #table) AS SourceTable
    pivot
    (
        sum([Hours])
        for [Year] IN ([2010], [2011], [2012])
    ) as PivotTable

结果:

2010    2011    2012
175     225     600

Answer 4

(a) 你也许能以某种方式获得数据,但这样做也许有帮助:

 select sum(p.TotalHours), p.Year From someTable p
    GROUP BY p.Year

b. 您应该做一些逻辑推理,将列编成行和列。

Answer 5

select sum("Total Hours") as "Total Hours", Year 
from tablename 
group by Year

此简单查询将为您提供所需的输出。

友情链接