SQL inserting data into a temp table, splitting a column

Question

I am trying to get data from a table and split the information into two columns in a temp table but I'm having a spot of trouble with it. Let me explain.

table : a    
+-------+-----------+-----------+
|   ID  |    Type   |    Word   |     
+-------+-----------+-----------+
|   1   |    Fr     |   Wee     |   
|   1   |    Eng    |   Yes     |   
|   2   |    Fr     |   Non     |  
|   2   |    Eng    |   No      |   
|   3   |    Fr     |   Bien    |  
|   3   |    Eng    |   Good    |
+-------+-----------+-----------+

I have the above table and I want to insert the word data into a temp table but I want to split it into French words and English Words. I have used the following.

CREATE TABLE #translation
(
French NVARCHAR(50),
English NVARCHAR(50)
)

INSERT INTO #translation (French)
SELECT Word FROM a
WHERE Type = 'Fr'

INSERT INTO #translation (English)
SELECT Word FROM a
WHERE Type = 'Eng'

This sort of works but produces:

+-------+-----------+-----------+
|  Row  |  French   |  English  |     
+-------+-----------+-----------+
|   1   |    Wee    |   NULL    |   
|   2   |    Non    |   NULL    |   
|   3   |    Bien   |   NULL    |  
|   4   |    NULL   |   Yes     |   
|   5   |    NULL   |   No      |  
|   6   |    NULL   |   Good    |
+-------+-----------+-----------+

Ideally I want them side by side.

Any tips for this?

If more information is needed please ask.

Answer 1

One method is conditional aggregation:

INSERT INTO #translation (French, English)
    SELECT MAX(CASE WHEN Type = 'FR' THEN Word END),
           MAX(CASE WHEN Type = 'EN' THEN Word END)       
    FROM a
    GROUP BY id;