Row as column in SQL Server 2008

Question

I am working with SQL Server 2008. I have a temp table which returns the this result

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

I want to get the result as below

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

How to query to get the above result using SQL Server 2008?

Please provide your current query and if possible organise resulting data in more convenient way to read (with headers, separators) — Ivan Gerasimenko, Feb 25 '15 at 12:34

score 0 · Accepted Answer · edited May 23 '17 at 10:24

You can use this query, you have to complete it to fit your needs with all missing months/quarters:

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

There's also the PIVOT method:

Using PIVOT in SQL Server 2008

MSSQL dynamic pivot column values to column header

Thanks a lot it helps..how to get the column wise total using this query — user2514925, Feb 25 '15 at 12:59
Add another SUM(value) without any test gives you grand total (updated answer). — Rubik, Feb 25 '15 at 13:04
sorry i have mentioned wrongly as columnwise i want to get rowwise total — user2514925, Feb 25 '15 at 13:24

Row as column in SQL Server 2008

1 Answers1