SQL to transpose row pairs to columns in MS ACCESS database

sql ms-access rows transpose
Marek Jedliński picture Marek Jedliński · Apr 10, 2009 · Viewed 39.7k times · Source

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

Answer

Fionnuala picture Fionnuala · Apr 10, 2009

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

Related questions

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More