SQL 将行对转换为 MS ACCESS 数据库中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/736317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL to transpose row pairs to columns in MS ACCESS database
提问by Marek Jedliński
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:
我有一个 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...
+---+----+--------------+
What SQL could I use to turn that into a table such as:
我可以使用什么 SQL 将其转换为表，例如：
+---+--------------+--------------+
|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).
性能在这里并不重要，因为我只需要偶尔这样做一次，而且数据库并不大（只有几千行）。
回答by Fionnuala
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
回答by Milen A. Radev
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'
回答by tpdi
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.
其中“table”是它们所在的任何表。
回答by Swathi
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
