Constructing a PIVOT

Question

I think that PIVOT will help me accomplish this, but I can't get anything started. I am having serious SQL brain farts today, I need some help.

Here is the output I have now:

Id    Name  Question    Answer
0     Test  Vault       A
0     Test  Container   1
1     Foo   Vault       B
1     Foo   Container   2

And this is my desired output:

Id   Name Vault Container
0    Test A    1
1    Foo  B    2

Can this be done?

If that is impossible or terribly complex to do, I have an alternate way to approach this. The output for my alternate query is:

Id   Name VaultId ContainerId
0    Test A       NULL
0    Test NULL       1
1    Foo  B       NULL  
1    Foo  NULL       2

And here I need to be able to suppress it into one row per Id/Name. I can't remember how to do either of these!

Answer 1

DECLARE @Test TABLE
(
     Id      INT 
    ,[Name]VARCHAR(10) NOT NULL
    ,Question       VARCHAR(10) NOT NULL,
    Answer VARCHAR(10)

);  
INSERT  @Test   VALUES (0,'test1', 'vault','a');
INSERT  @Test   VALUES (0,'test1', 'Container ','1');
INSERT  @Test   VALUES (1,'test4', 'vault','b');
INSERT  @Test   VALUES (1,'test4', 'Container','2');



;WITH CTE
AS
(
    SELECT  t.id, t.[Name], t.[Question    ]   ,t.Answer
     FROM    @Test t
)

SELECT  * 
FROM    CTE
 PIVOT   ( max(answer) FOR Question     IN (vault,container) )  f;