SQL to transpose row pairs to columns in MS ACCESS database

I have an MS Access database that contains translated sentences in source-target pairs (a translation memory for fellow users of CAT tools). Somewhat annoyingly, source and target are not stored in separate columns, but in rows linked by ID, like this:

+---+----+--------------+
|id |lang|    text      |
+---+----+--------------+
  1   a     lang a text
  1   b     lang b text 
  2   a     more a text...
  2   b     more b text...
+---+----+--------------+

What SQL could I use to turn that into a table such as:

+---+--------------+--------------+
|id | lang A       | lang B       |
+---+--------------+--------------+
 1   lang a text    lang b text
 2   more a text... more b text...

Performance doesn't matter here, since would I only need to do this once in a while, and the db isn't huge (only a few thousand rows).

From stackoverflow

• You need a self-join:

  SELECT
      t1.id, t1.text AS lang_a, t2.text AS lang_b
  FROM
      lang_table AS t1
  INNER JOIN
      lang_table AS t2
  ON
      (t1.id = t2.id)
  WHERE
      t1.lang = 'a'
  AND
      t2.lang = 'b'
  

  moodforaday : In Access this produces a table that has the correct columns, but both lang_a and lang_b columns contains the same language text (lang_a). It just shows the same lang_a of each record twice. I realize this violates the WHERE condition, but this is what I'm getting. (contd.)

  moodforaday : (contd.) Adding t1.lang and t2.lang to the select shows the correct language ids. I've gone over this several times but haven't been able to figure out the problem.

• A crosstab query should suit.

  TRANSFORM First([Text]) AS LangText
  SELECT ID, First([Text])
  FROM Table 
  GROUP BY ID
  PIVOT lang
  

  Further information: http://allenbrowne.com/ser-67.html

  moodforaday : Thanks, I didn't know about TRANSFORM. It produces exactly the result I need. Access seems to add a column named "Expr1003", with the same content as language a (source), but I can just ignore it.

  Remou : You can just alter the SELECT line to read: SELECT ID

• select a.id, a.text as 'lang A', b.text as 'lang B'
  from table a join table b on (a.id = b.id)
  where a.lang = 'a' and b.lang = 'b';
  

  where "table" is whatever table these are in.

• select a.id, MAX(case when a.lang like 'a' then a.text) as Lang A, MAX(case when a.lang like 'a' then a.text) as Lang A From table a group by a.id