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          |
+------------+---------------+-----------------+--------------+--------------+

I don't understand your table layout, could you try improving it by using [this](http://ozh.github.io/ascii-tables/)? — marijnz0r, Jun 09 '15 at 14:28
possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Bernd Linde, Jun 09 '15 at 14:33
It sounds like you're looking for a Self Join: http://stackoverflow.com/questions/2458519/explanation-of-self-joins — Mark Leiber, Jun 09 '15 at 14:34
And what if there's also a third language for an xreference? — jarlh, Jun 09 '15 at 14:35

score 2 · Answer 1 · answered Jun 09 '15 at 14:38

2

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.

answered Jun 09 '15 at 14:38

Gordon Linoff

1,242,037
58
646
786

I want all the values stored in my table not only max (or min or count). It is possible to perform this conditional aggregation without these operators? – madmad Jun 09 '15 at 14:47
@madmad, this will give you max of one value, i.e. the only value! – jarlh Jun 09 '15 at 14:57
@madmad . . . This assumes that there is at most one English title and at most one Italian title for each `xreference`. If this is not true, you might want `group_concat()` instead. – Gordon Linoff Jun 10 '15 at 02:07

score 0 · Answer 2 · answered Jun 09 '15 at 14:51

0

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'

answered Jun 09 '15 at 14:51

Sotirca Mihaita George

238
1
7

Just a comment, this will only return xreference's having both English and Italian languages. If one language is missing, nothing will be returned. – jarlh Jun 09 '15 at 14:57
Of course. He can use the outer join depending on what he wants to obtain. – Sotirca Mihaita George Jun 09 '15 at 15:07
Too bad MySQL doesn't have full outer join. – jarlh Jun 10 '15 at 06:17

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

2 Answers2