How do I create a sql select statement where the select column names are values in rows of another table (I am using postgresql)? I have a language table with language codes:
如何创建一个sql select语句,其中select列名是另一个表的行中的值(我正在使用postgresql)?我有一个语言表与语言代码:
language
========
id code name
-------------------
1 en English
2 fr French
3 it Italian
country
=======
id en fr it other_columns
------------------------------------
1 ...
2 ...
I want to select the id and all the language columns from the country table and they are listed in the language table. Something like:
我想从国家/地区表中选择id和所有语言列,它们列在语言表中。就像是:
SELECT id, (SELECT code FROM language) FROM country
so I effectively end up with:
所以我有效地结束了:
SELECT id, en, fr, it from country
Thanks!
2 个解决方案
#1
3
This is known as a "pivot" or "crosstab" and is something SQL is notoriously bad for. PostgreSQL offers an extension that helps a bit, though it's not beautiful to use - take a look at the crosstab
function in the tablefunc
extension.
这被称为“枢轴”或“交叉表”,并且SQL是众所周知的坏事。 PostgreSQL提供了一个有点帮助的扩展,虽然使用起来并不漂亮 - 看一下tablefunc扩展中的交叉表函数。
A search for the crosstab or pivot tags along with the postgresql tag will find you more. Eg:
搜索交叉表或数据透镜标签以及postgresql标签会发现更多信息。例如:
#2
0
This is a partial answer. The following line gives you the SQL you are looking for. Perhaps PL/pgSQL's EXECUTE could actually run it for you.
这是部分答案。以下行为您提供了所需的SQL。也许PL / pgSQL的EXECUTE实际上可以为你运行它。
SELECT 'SELECT id, '
|| (SELECT array_to_string(array_agg(code),', ') FROM LANGUAGE)
|| 'FROM country';
Some test data:
一些测试数据:
CREATE TABLE country (id integer, en text, fr text, it text, es text, de text);
INSERT INTO country (id, en, fr, it, es, de)
VALUES (1, 'Hello', 'Bonjour', 'Buon giorno', 'Buenas dias', 'Guten Tag');
CREATE TABLE language (id integer, code text, name text);
INSERT INTO language (id, code, name)
VALUES (1, 'en', 'English'), (2, 'fr', 'French'), (3, 'it', 'Italian');
#1
3
This is known as a "pivot" or "crosstab" and is something SQL is notoriously bad for. PostgreSQL offers an extension that helps a bit, though it's not beautiful to use - take a look at the crosstab
function in the tablefunc
extension.
这被称为“枢轴”或“交叉表”,并且SQL是众所周知的坏事。 PostgreSQL提供了一个有点帮助的扩展,虽然使用起来并不漂亮 - 看一下tablefunc扩展中的交叉表函数。
A search for the crosstab or pivot tags along with the postgresql tag will find you more. Eg:
搜索交叉表或数据透镜标签以及postgresql标签会发现更多信息。例如:
#2
0
This is a partial answer. The following line gives you the SQL you are looking for. Perhaps PL/pgSQL's EXECUTE could actually run it for you.
这是部分答案。以下行为您提供了所需的SQL。也许PL / pgSQL的EXECUTE实际上可以为你运行它。
SELECT 'SELECT id, '
|| (SELECT array_to_string(array_agg(code),', ') FROM LANGUAGE)
|| 'FROM country';
Some test data:
一些测试数据:
CREATE TABLE country (id integer, en text, fr text, it text, es text, de text);
INSERT INTO country (id, en, fr, it, es, de)
VALUES (1, 'Hello', 'Bonjour', 'Buon giorno', 'Buenas dias', 'Guten Tag');
CREATE TABLE language (id integer, code text, name text);
INSERT INTO language (id, code, name)
VALUES (1, 'en', 'English'), (2, 'fr', 'French'), (3, 'it', 'Italian');