SQL to transpose row pairs to columns in MS ACCESS database

Question

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).

score 7 · Accepted Answer · answered Apr 10 '09 at 00:26

7

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

answered Apr 10 '09 at 00:26

Fionnuala

90,370
7
114
152

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. – Marek Jedliński Apr 10 '09 at 15:48

score 2 · Answer 2 · answered Apr 10 '09 at 00:25

2

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'

answered Apr 10 '09 at 00:25

Milen A. Radev

60,241
22
105
110

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.) – Marek Jedliński Apr 10 '09 at 15:44
(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. – Marek Jedliński Apr 10 '09 at 15:46

score 0 · Answer 3 · edited Dec 15 '12 at 10:25

0

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

edited Dec 15 '12 at 10:25

VDP

6,340
4
31
53

answered Aug 09 '10 at 21:08

Swathi

1

score 0 · Answer 4 · answered Apr 10 '09 at 00:26

0

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.

answered Apr 10 '09 at 00:26

tpdi

34,554
11
80
120

SQL to transpose row pairs to columns in MS ACCESS database

4 Answers4

Linked