Querying different result from the same row

Question

so I have this table. I need from it - translated_name in US, translated_description also in US, translated_name in RU and translated_description in RU. And this is the problem - I need to query 4 results from two rows... I have tried several options, including UNION ALL, but it orders them one below the another - I need 4 columns next to each other... Thank you in advance!

(SELECT 
    TRANSLATED_NAME AS US_NAME,
    TRANSLATED_DESCRIPTION AS US_DESC
FROM PRODUCT_DESCRIPTIONS PD
WHERE PD.TRANSLATED_NAME LIKE '%Monitor%' 
AND PD.LANGUAGE_ID = ('US'))

UNION ALL

(SELECT
    TRANSLATED_NAME AS RU_NAME, 
    TRANSLATED_DESCRIPTION AS RU_DESCRIPTION
FROM PRODUCT_DESCRIPTIONS PD
WHERE  PD.LANGUAGE_ID = 'RU'
AND PD.TRANSLATED_NAME LIKE '%Монитор%');

Please, [**do not post images of data**](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question#285557) and moreover a screenshot of a table columns. Provide a sample data and desired output in text format (or as `insert` statements) — astentx, Sep 15 '21 at 07:41
Does this answer your question [How to convert Rows to Columns in Oracle?](https://stackoverflow.com/questions/19858079/how-to-convert-rows-to-columns-in-oracle) — astentx, Sep 15 '21 at 08:01

score 0 · Answer 1 · answered Sep 15 '21 at 07:25

0

Use CASE:

select case when language_id = 'US' then translated_name end as us_name,
       case when language_id = 'US' then translated_description end as us_description,
       --
       case when language_id = 'RU' then translated_name end as ru_name,
       case when language_id = 'RU' then translated_description end as ru_description
from product_descriptions
where ...

answered Sep 15 '21 at 07:25

Littlefoot

131,892
15
35
57

Without any sample data, I can't run that query so - no idea. – Littlefoot Sep 15 '21 at 07:36
CREATE TABLE and INSERT INTO sample rows, that's how. – Littlefoot Sep 15 '21 at 07:51
What's that? Screenshot you posted suggests different, table named PRODUCT_DESCRIPTIONS with 4 columns: PRODUCT_ID, LANGUAGE_ID, TRANSLATED_NAME, TRANSLATED_DESCRIPTION. I have no idea what is "FFF" you posted. Besides, it looks like what you'd want to get as result, not sample data (i.e. the *source*). – Littlefoot Sep 15 '21 at 08:12
@NNikolow please [edit](https://stackoverflow.com/posts/69188685/edit) your original question and add all the required details in its body. Comments are not for such essential stuff. Also please check [sql tag wiki](https://stackoverflow.com/tags/sql/info) for more information on how to prepare sample data for the question to make it answerable – astentx Sep 15 '21 at 08:15
yes this is the result I want to get, the source table is this CREATE TABLE "OE"."PRODUCT_DESCRIPTIONS" ( "PRODUCT_ID" NUMBER(6,0), "LANGUAGE_ID" VARCHAR2(3 BYTE), "TRANSLATED_NAME" NVARCHAR2(50), "TRANSLATED_DESCRIPTION" NVARCHAR2(2000) – NNikolow Sep 15 '21 at 08:17
Please read previous comment by @astentx, regarding updating original question, etc. – EdStevens Sep 15 '21 at 14:31

Querying different result from the same row

1 Answers1