Create column from query data

Question

Test data:

create temp table l (id integer,name text);
create temp table t (id integer);
create temp table t_i18n(id integer,l_id integer,t_id integer,name text);
insert into l(id, name) values
(1, 'lang_1'),
(2, 'lang_2');
insert into t(id) values(1);
insert into t_i18n(id, l_id, t_id, name) values 
(1, 1, 1, 'Text in the language one'),
(2, 2, 1, 'Text in the language two');

After execution of this query:

select *
from t
inner join t_i18n i18n
on i18n.t_id = t.id;

I have this result:

 id | id | l_id | t_id |           name           
----+----+------+------+--------------------------
  1 |  1 |    1 |    1 | Text in the language one  
  1 |  2 |    2 |    1 | Text in the language two

Is it possible to modify query above to obtain result below?

/*Expected result*/
 id | name_lang_1              | name_lang_2
----+--------------------------+--------------------------
  1 | Text in the language one | Text in the language two

Looks like a variant on the [tag:crosstab] or [tag:pivot] problem. Try searching on those tags and the `[postgresql]` tag. — Craig Ringer, Jul 26 '13 at 13:26
I addition to Craig's comment: check out the `crosstab` function in the [tablefunc](http://www.postgresql.org/docs/current/static/tablefunc.html) module — , Jul 26 '13 at 14:14

score 1 · Answer 1 · answered Jul 26 '13 at 16:15

Use a self join

select  t1.id,    
        i18n1.l_id l_id1,
        i18n1.t_id t_id1,
        i18n1.name name1,
        i18n2.l_id l_id2,
        i18n2.t_id t_id2,
        i18n2.name name2
from t t1
inner join t_i18n i18n1 on i18n1.t_id = t1.id and i18n1.l_id = 1
inner join t t2 on t1.id = t2.id
inner join t_i18n i18n2 on i18n2.t_id = t2.id and i18n2.l_id = 2
;

However it works only on the assumption that each record in t table always has exactly two corresponding records in t_i18n table (one record for each language).
If some records could have only one language (lang-1 or lang-2), then you must use outer joins like in this query:

select  t1.id,    
        i18n1.l_id l_id1,
        i18n1.t_id t_id1,
        i18n1.name name1,
        i18n2.l_id l_id2,
        i18n2.t_id t_id2,
        i18n2.name name2
from t t1
left join t_i18n i18n1 on i18n1.t_id = t1.id and i18n1.l_id = 1
full outer join t t2 on t1.id = t2.id
left join t_i18n i18n2 on i18n2.t_id = t2.id and i18n2.l_id = 2

Here is an SQLFiddle demo with sample data for second case and both queries, take a look how they works on this data.

Little more complicated: number of columns in table `l` is not fixed and aliases (`lang_1`, `lang_2`) must be generated automatically based on `l.id` value. Is it possible ? — cetver, Jul 26 '13 at 20:56

score 1 · Accepted Answer · edited May 23 '17 at 12:29

Generally crosstab() will be fastest. You need the additional module tablefunc installed in your database.

SELECT * FROM crosstab(
   'SELECT t.id, l.name AS lang_name, i.name AS lang
    FROM   t
    JOIN   t_i18n i ON i.t_id = t.id
    JOIN   l  ON l.id = i.l_id'  -- could also just be "ORDER BY 1" here

   ,$$VALUES ('lang_1'::text), ('lang_2')$$)
AS l (id text, lang_1 text, lang_2 text);

If your case is actually as simple as that (it hardly ever is), a query with CASE statements would do:

SELECT t.id
      , min(CASE WHEN i.l_id = 1 THEN i.name ELSE NULL END) AS lang_1
      , min(CASE WHEN i.l_id = 2 THEN i.name ELSE NULL END) AS lang_2
FROM               t
LEFT   JOIN t_i18n i ON i.t_id = t.id
LEFT   JOIN        l ON l.id = i.l_id
GROUP  BY 1
ORDER  BY 1;

Details for both solutions under this related question:
PostgreSQL Crosstab Query

Since every SQL query and every function must have a well defined return type, it is not possible to do this for a dynamic number of language names in a single query. You could write a function to create the statement dynamically and execute the statement in a second call.

There are also advanced techniques with polymorphic types, I have written a comprehensive answer here:
Dynamic alternative to pivot with CASE and GROUP BY

However, the simple crosstab() query above works well with a superset of language names. Fields for non-existant languages return NULL instead. Have a look at the provided link.

Little more complicated: number of columns in table `l` is not fixed and aliases (`lang_1`, `lang_2`) must be generated automatically based on `l.id` value. Is it possible ? — cetver, Jul 26 '13 at 20:55

Create column from query data

2 Answers2