CODEWITHSUNDEEP
sqlpostgresqlpivot
webcoyote
webcoyote

Reputation: 41

SQL:Select columns based on rows of another table

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:

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:

SELECT id, (SELECT code FROM language) FROM country

so I effectively end up with:

SELECT id, en, fr, it from country

Thanks!

Upvotes: 4

Views: 4536

Answers (2)

Ian Gow
Ian Gow

Reputation: 3535

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.

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');

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324475

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.

A search for the or tags along with the tag will find you more. Eg:

Upvotes: 2

Related Questions