How do I achieve Pivot in SQL Server Compact Edition?

Question

I'm trying to use pivot in SQL Server Compact Edition 3.5, but since the Compact editions don't support pivot how do I do this?

Table: Donate

    | Name  | Date            | Amount |
    | James | 27/7/2015 20:19 | 500    |
    | Owen  | 27/7/2015 20:20 | 400    |
    | James | 27/7/2015 20:21 | 100    |

And so with the following query I can do pivot with no problems

select *
from
    (select Name, datename(month, Date) as mname, Amount
     from Donate) as test
pivot(
    sum(Amount)
    for mname in ([July])
) as pvt

And get this which is perfect

    | Name  | July |
    | James | 600  |
    | Owen  | 400  |

But when I use the code is SQL Server compact editions it gives me an error that there is no pivot function. So I researched for a solution and found out that pivot doesn't exist in SQL Server CE.

So anyone know how can I do this in SQL Server Compact Editions? Thanks in advance

Answer 1

Does this work?

Select name,
sum(case datename(month, Date) when 'January' then Amount else 0 end) as Jan,
sum(case datename(month, Date) when  'February' then Amount else 0 end) as Feb,
...
...
From Donate
group by name