--========= 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');

declare @a table ([User] int, [Year] int, 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 
x.*
From 
	@a x
	JOIN @a y on x.[User]=y.[User]
				and x.[Year] =y.Year+1
Where
	(x.Status1=y.Status1 and x.Status1='Fail')
	or (x.Status2=y.Status2 and x.Status2='Fail')
	or (x.Status3=y.Status3 and x.Status3='Fail')

declare @a table ([User] int, [Year] int, 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 ( 3, 2011, 'Pass', 'Fail', 'Pass' );
insert @a values ( 1, 2012, 'Pass', 'Fail', 'Pass' );
insert @a values ( 2, 2012, 'Pass', 'Fail', 'Pass' );
insert @a values ( 3, 2012, 'Pass', 'Pass', 'Pass' );
insert @a values ( 1, 2013, 'Pass', 'Pass', 'Pass' );
insert @a values ( 2, 2013, 'Pass', 'Pass', 'Pass' );
insert @a values ( 3, 2013, 'Pass', 'Fail', 'Pass' );

;WITH CTE AS (
    SELECT [User], [Year], n, status
    FROM @a
    CROSS APPLY (
        VALUES (1, Status1), (2, Status2), (3, Status3)) a(n, [status])
    WHERE status = 'Fail')
SELECT a.[User], [Status]=a.n
FROM CTE a
INNER JOIN CTE b ON a.[User] = b.[User] AND a.n = b.n AND a.[Year] = b.[Year] - 1