SQL: How to partition data by the key and transpose the triples in the defined order? (The tool for a human-language translator.)

I do have the table with translated texts to (possibly) many languages. When new text is entered in the chosen language, the lang_hash is calculated. If the text is not bound to any existing text in other languages, the lang_hash is repeated in the key_hash, and–together with the lang code–the new record is inserted. For the Czech, the English, and the German languages, the content of the table could look like this:

key_hash      lang  lang_hash       lang_text
0x38E6AA2C5C    de  0x38E6AA2C5C    die Orange
0x38E6AA2C5C    en  0xFBD2896D64    the orange

0x4DD9B471C8    en  0x4DD9B471C8    tomato

0x662A839B85    de  0x662A839B85    die Melone
0x662A839B85    en  0xD79A09DA7E    the watermelon

0x6A0D439BF6    de  0x6A0D439BF6    die Birne

0x6F1EDE3767    cs  0x0541D7A777    jablko
0x6F1EDE3767    de  0x6F1EDE3767    der Apfel
0x6F1EDE3767    en  0xE50ED37D59    the apple

0xA5FC751D4B    cs  0xA5FC751D4B    ananas

0xC0D6B4BECA    cs  0x1ED2658D57    banán
0xC0D6B4BECA    de  0xC0D6B4BECA    die Banane

The cs and en languages are meant to be obligatory. Here the de language is meant to be filled by a human translator. Anyway, a German user is also allowed to enter some new terms. The first entered term defines the key. I have separated the records with the same key.

From time to time, the human translator should look at the data and fill or the obligatory cs and en terms, or to add the de translation for the cs and en equivalents.

He or she should always works with exactly three languages: cs, en, and the chosen one. Say, the de is the chosen one for now. The translator should see a table like this (the key_hash will not be visible):

The task is to enter all the empty (pink) fields.

How can I write the SQL query to get the result organized as shown on the picture, with NULL values for the empty fields? Is there any elegant way to partition the data based on the key_hash and then transpose the language records in the order of cs, en, de?

Update: So far, I have the following query that shows what I need. However, it seems a bit uggly to me. And also, I will have much more data in future, and I am not sure it would work for them. (Sorry for the Czech identifiers: klic means key, jazyk means language, preklady means translations)

DECLARE @lang varchar(2) = 'de'

SELECT COALESCE(cs.klic_hash, en.klic_hash, xx.klic_hash) AS klic_hash,
       cs.jazyk_text AS cs_text,
       en.jazyk_text AS en_text, 
       xx.jazyk_text AS xx_text
  FROM (SELECT klic_hash, jazyk_text FROM preklady WHERE jazyk = 'cs') AS cs
       FULL OUTER JOIN (SELECT klic_hash, jazyk_text FROM preklady WHERE jazyk = 'en') AS en
            ON cs.klic_hash = en.klic_hash 
       FULL OUTER JOIN (SELECT klic_hash, jazyk_text FROM preklady WHERE jazyk = @lang) AS xx
            ON COALESCE(cs.klic_hash, en.klic_hash) = xx.klic_hash

Answer

You need conditional aggregation:

DECLARE @lang char(2) = 'de'

SELECT klic_hash,
       MIN(CASE WHEN jazyk = 'cs'  THEN cs.jazyk_text END) AS cs_text,
       MIN(CASE WHEN jazyk = 'en'  THEN cs.jazyk_text END) AS en_text, 
       MIN(CASE WHEN jazyk = @lang THEN cs.jazyk_text END) AS xx_text
FROM preklady
GROUP BY klic_hash;

Advertisement

Answer