cetver cetver - 29 days ago 11
SQL Question

Create column from query data

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

Answer Source

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.