SQL:选择列作为<另一列的值> [英] SQL: Select column as <another column's value>

问题描述

我有一张桌子

ID | LanguageID | Text
1  | 1          | Abc
1  | 2          | Def

我想选择数据，以便将具有相同 ID 的所有行组合到一行中，并将每个文本选择到具有其 LanguageID 名称的列中.在这个特定的例子中，结果是

I want to select data so that all rows with the same ID are grouped into one row and each text is selected into a column with the name of its LanguageID. In this particular example the result would be

[ID: 1, 1: 'Abc', 2: 'Def']

这能做到吗?如何?谢谢

Can this be done? How? Thank you

我正在使用 MySQL.

I am using MySQL.

似乎最简单的方法是编写一个脚本来合并数据库外的行.

Seems like the easiest thing to do is to write a script to merge the rows outside of the database.

我不知道该接受什么答案，因为没有一个有效或满足上述要求.

I don't know what answer to accept since none works or meets the requirements above.

推荐答案

如果你有固定数量的语言 ID，那么你可以这样做:

If you have a fixed number of language ids, then you can do this as:

select id,
       max(case when LanguageId = 1 then text end) as "1",
       max(case when LanguageId = 2 then text end) as "2",
       max(case when LanguageId = 3 then text end) as "3"
from t
group by id;

如果您事先不知道语言 id，那么您需要使用动态 SQL 来构造查询，然后准备并执行它.

If you don't know the language ids in advance, then you need to use dynamic SQL to construct the query and then prepare and execute it.

如果 languageid 是动态的:

If languageid is dynamic:

select @s = concat('select id',
                   group_concat(concat(', max(case when LanguageId = ',
                                       LanguageId,
                                       ' then text end) as "',
                                       LanguageId, '"'
                                      )
                               ),
                   ' from t group by id'
                  )
from (select distinct LanguageId from t) t;

PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

这篇关于SQL:选择列作为<另一列的值>的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！