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

问题描述

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

+---+----+--------------+|id |语言|文字 |+---+----+--------------+1 a lang a text1 b lang b 文本2 a more a text...2 b 更多 b 文本...+---+----+--------------+

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

+---+--------------+--------------+|身份证 |朗A |朗B |+---+--------------+--------------+1 lang a text lang b text2 more a text... more b text...

性能在这里并不重要，因为我只需要偶尔执行一次，而且数据库并不大(只有几千行).

解决方案

交叉表查询应该适合.

TRANSFORM First([Text]) AS LangTextSELECT ID, First([文本])发件人表按 ID 分组枢轴

更多信息: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屋！