SQL将行对转置为MS ACCESS数据库中的列 [英] SQL to transpose row pairs to columns in MS ACCESS database

问题描述

我有一个MS Access数据库，其中包含源-目标对中的翻译句子(CAT工具其他用户的翻译记忆库).令人讨厌的是，源和目标没有存储在单独的列中，而是存储在按ID链接的行中，如下所示:

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

我可以使用什么SQL将其转换为表，例如:

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

这里的性能无关紧要，因为我只需要偶尔执行一次，数据库就不会很大(只有几千行).

解决方案

交叉表查询应该适合.

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

更多信息: http://allenbrowne.com/ser-67.html

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

解决方案

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

这篇关于SQL将行对转置为MS ACCESS数据库中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！