Couple ways to go about this...I always find dynamic sql fun, so thought I'd share.

Also, I can imagine you might only want to show a few years at a time or it might get unruly, but if you need to add more you can do that easily.

declare @min_year as int

, @sql as nvarchar(max)

select @min_year = 2012

set @sql =

'select acc.accounts

, case when yr1.revenue is not null then yr1.revenue end as ''' + cast(@min_year as char(4)) + '_revenue''

, case when yr1.margin is not null then yr1.margin end as ''' + cast(@min_year as char(4)) + '_margin''

, case when yr2.revenue is not null then yr2.revenue end as ''' + cast(@min_year+1 as char(4)) + '_revenue''

, case when yr2.margin is not null then yr2.margin end as ''' + cast(@min_year+1 as char(4)) + '_margin''

, case when yr3.revenue is not null then yr3.revenue end as ''' + cast(@min_year+2 as char(4)) + '_revenue''

, case when yr3.margin is not null then yr3.margin end as ''' + cast(@min_year+2 as char(4)) + '_margin''

from (SELECT DISTINCT ACCOUNTS FROM Table1 ) ACC

left join (select accounts

, Revenue

, Margin

from Table1

where year = ''' + cast(@min_year as char(4)) + ''') yr1

on acc.accounts = yr1.accounts'

+ '

left join (

select accounts

, Revenue

, Margin

from Table1

where year = ''' + cast(@min_year+1 as char(4)) + ''') yr2

on acc.accounts = yr2.accounts'

+ '

left join (

select accounts

, Revenue

, Margin

from Table1

where year = ''' + cast(@min_year+2 as char(4)) + ''') yr3

on acc.accounts = yr3.accounts'

--print @sql

execute (@sql);