Is there any way to avoid sub queries?

Question

I have following sample data in TEST table:

CREATE TABLE TEST(
f1 varchar(50) NULL,
f2 varchar(50) NULL,
flag int NULL);

INSERT INTO [test]([f1],[f2],[flag]) VALUES('P','a',1);
INSERT INTO [test]([f1],[f2],[flag]) VALUES('P','b',0);
INSERT INTO [test]([f1],[f2],[flag]) VALUES('Q','c',1);
INSERT INTO [test]([f1],[f2],[flag]) VALUES('Q','d',0);
INSERT INTO [test]([f1],[f2],[flag]) VALUES('R','e',1);
INSERT INTO [test]([f1],[f2],[flag]) VALUES('S','f',0);

I want this result:

f1  f2whenFlagIs1   f2whenFlagIs0
P   a               b
Q   c               d
R   e               NULL
S   NULL            f

the following query has been written to have the same results:

SELECT isnull(test1.f1,test0.f1) f1, test1.f2 f2whenFlagIs1 , test0.f2 AS f2whenFlagIs0
FROM 
(select * from test where flag = 1) AS test1  full JOIN
(select * from test where flag = 0) AS test0 
ON test1.f1 = test0.f1

Is there any way to avoid sub queries?

Answer 1

Here are two ways, given the sample data, assuming that there can only be one value for every combination of f1 + flag:

SELECT f1, 
  f2WhenFlagIs1 = MAX(CASE WHEN flag = 1 THEN f2 END),
  f2WhenFlagIs0 = MAX(CASE WHEN flag = 0 THEN f2 END)
FROM dbo.TEST
GROUP BY f1;

Or

SELECT f1, f2WhenFlagIs1 = [1], f2WhenFlagIs0 = [0]
FROM dbo.TEST AS t
PIVOT (MAX(f2) FOR flag IN ([0],[1])) AS p;

If you can have more than one value for any given f1, flag pair, you'll need to better define your desired results.