How about this?

--========= TEST DATA =============

declare @a table ([User] int, [Year] Char(4), Status1 Char(4), Status2 Char(4), Status3 Char(4));

insert @a values ( 1, 2011, 'Pass', 'Fail', 'Fail' );

insert @a values ( 2, 2011, 'Pass', 'Pass', 'Pass' );

insert @a values ( 1, 2012, 'Pass', 'Fail', 'Pass' );

insert @a values ( 2, 2012, 'Pass', 'Fail', 'Pass' );

select * from @a;

-- The query would select all records like User #1 because they had two years in a row

-- where they "Failed" in the Status2 category. The user would have to fail a specific

-- Status category 2 consecutive years in a row.

--========= SOLUTION =============

select *

from

(

select [User],

Year1=MAX(case when [Year]='2011' then [Year] end),

Year2=MAX(case when [Year]='2012' then [Year] end),

Status1Year1=MAX(case when [Year]='2011' then [Status1] end),

Status1Year2=MAX(case when [Year]='2012' then [Status1] end),

Status2Year1=MAX(case when [Year]='2011' then [Status2] end),

Status2Year2=MAX(case when [Year]='2012' then [Status2] end),

Status3Year1=MAX(case when [Year]='2011' then [Status3] end),

Status3Year2=MAX(case when [Year]='2012' then [Status3] end)

from @a

group by [User]

) z

where (Status1Year1='Fail' and Status1Year2='Fail')

or (Status2Year1='Fail' and Status2Year2='Fail')

or (Status3Year1='Fail' and Status3Year2='Fail');