在SQL Server 2008中列为行

debugcn 发表于 Dev

用户名

我正在使用SQL Server2008。我有一个临时表，该表返回此结果

Location      Month        value
US          January         10
US          February        10
US          March           10
US          April           10
US          May             10
US          June            10
UK          January         10
UK          January         10
UK          February        10
UK          February        10
UK          March           10
UK          March           10
UK          April           10
UK          April           10
UK          May             10
UK          May             10
UK          June            10
UK          June            10

我想得到如下结果

Location    January February    March   Q1  April   May June    Q2
US              10        10       10   30     10    10   10    30
UK              20        20       20   60     20    20   20    60

如何使用SQL Server 2008查询以获得以上结果？

鲁比克

您可以使用此查询，您必须在缺少所有月份/季度的情况下完成它以满足您的需求：

select  Location
        ,sum(case when [Month]  = 'January'  then value else 0 end)  as January
        ,sum(case when [Month]  = 'February' then value else 0 end)  as February
        ,sum(case when [Month]  = 'March'    then value else 0 end)  as March
        ,sum(case when [Month] in ( 'January', 'February', 'March')
                                             then value else 0 end) as Q1
        ...
        -- Repeat months...
        ...
        ,sum(value) as AllMonthTotal
from    myTempTable
group by Location

-- UNION to get rowwise total
union
select  'TOTAL'
        ,sum(case when [Month]  = 'January'  then value else 0 end)  as January
        ,sum(case when [Month]  = 'February' then value else 0 end)  as February
        ,sum(case when [Month]  = 'March'    then value else 0 end)  as March
        ,sum(case when [Month] in ( 'January', 'February', 'March')
                                             then value else 0 end) as Q1
        ...
        -- Repeat months...
        ...
        ,sum(value) as AllMonthTotal
from    myTempTable