SQL - How to put beside two records of the same table in the same row

Question

I have a table with this structure:

+------------+-----------------+----------+
| xreference |      title      | language |
+------------+-----------------+----------+

I want that to obtain something like this by means of an SQL query :

+------------+---------------+-----------------+--------------+--------------+
| xreference |   title_eng   |    title_ita    | language_eng | language_ita |
+------------+---------------+-----------------+--------------+--------------+

how can i obtain this structure? There is a method that allow to organize two records of the same table in the same row?

For example, if i have this data:

+------------+-----------------+----------+
| xreference |      title      | language |
+------------+-----------------+----------+
|          1 | example_title   | eng      |
|          1 | example_title_2 | ita      |
+------------+-----------------+----------+

i want to obtain something like this:

+------------+---------------+-----------------+--------------+--------------+
| xreference |   title_eng   |    title_ita    | language_eng | language_ita |
+------------+---------------+-----------------+--------------+--------------+
|          1 | example_title | example_title_2 | eng          | ita          |
+------------+---------------+-----------------+--------------+--------------+

Answer 1

The simplest way in your case is probably conditional aggregation:

select xreference,
       max(case when language = 'eng' then title end) as title_eng,
       max(case when language = 'ita' then title end) as title_ita,
       'eng' as language_eng, 'ita' as language_ita
from thisdata t
group by xreference;

I am not sure what the last two columns are supposed to be doing.

The advantage to this method over using a join is two-fold. First, it is easy to add new languages. And, this will show all xreference values, even those that have no translations in English and/or Italian.

Answer 2

do a self join but without the subselect:

SELECT t1.title as title_eng, t2.title as title_ita
from table1 t1
join table2 t2 on (t2.xreference = t1.xreference)
where t1.language = 'eng' and t2.language = 'ita'