I have the following tables:
1. UNIT_OF_MEASURE : columns: UNIT_OF_MEASURE, CODE, DESCRIPTION, VERSION
2. UNIT_OF_MEASURE_TRANS: columns: UNIT_OF_MEASURE_ID_LANGAUGE_ID, DESCRIPTION
3. LANGUAGE: columns: LANGUAGE_ID, LANGUAGE_NAME
What I'm tried to do is to show all unit of measures descriptions in 5 languages. I've successfully done this but as a list, that is, all description in one column repeated in different languages.
SELECT
uomt.description,
l.language_name
FROM unit_of_measure_trans uomt
INNER JOIN language l ON (uomt.language_id = l.language_id)
WHERE
l.language_id IN (25, 22, 82, 34, 27, 52, 10, 90)
order by language_name;
`
Now, I need to improve this to show each group of descriptions in separated column based on the language. So I'll have five columns contain same group of unit of measure descriptions in different language. I tried slef-join but I got cartesian product results, not sure if union all will solve the issue. I've reviewed several posts about transpose here, I'm afraid I could not apply any of them on my case.
To emulate PIVOT, you can use a combination of CASE, MAX and GROUP BY:
SELECT
max(case when language_name = 'English' then uomt.description else null end)
as english_description,
max(case when language_name = 'German' then uomt.description else null end)
as german_description,
uomt.unit_of_measure_id
FROM unit_of_measure_trans uomt
INNER JOIN language l ON (uomt.language_id = l.language_id)
group by uomt.unit_of_measure_id
order by uomt.unit_of_measure_id;