I have the following Table with the following Data inside:

What I want is to get the Result in the following Format:

german="deutscher text"
english="english text"

This menas, it should be not:

text="deutscher text"
text="english text"

The Key/Column Name text should be the data from language

I tried the following Query but its not working:

SELECT text as (SELECT language FROM `table` where id = 1) FROM `table` where id = 1;

(SELECT language FROM table where id = 1) will return "german" so the Query should be: "SELECT text as german FROM table where id = 1;" but this is not working.

Is there a way to do this in one Query?

Cheers, Thomas

CodePudding user response：

You'll have to alter your table schema a bit; Added a ref to group the languages to use

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ref` int(11) DEFAULT 0,
  `text` varchar(50) DEFAULT NULL,
  `language` varchar(50) DEFAULT NULL,
  KEY `Index 1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

Then the SQL

SELECT T.text AS english, T2.text AS german
FROM test T 
INNER JOIN test T2 ON T.ref = T2.ref AND T2.`language` = 'german'
WHERE 
    T.ref = 1 AND
    T.language = 'english'

The dummy data

INSERT INTO `test` (`id`, `ref`, `text`, `language`) VALUES
    (1, 1, 'deutscher text', 'german'),
    (2, 1, 'english text', 'english');

CodePudding user response：

One option you can use is PREPARED STATEMENT:

SET @sql := NULL;

SELECT GROUP_CONCAT(
           CONCAT('MAX(CASE WHEN language="',language,'" THEN text END) AS "',language,'"')) 
         INTO @sql
FROM mytable;

SELECT CONCAT('SELECT ',@sql,' FROM mytable;') INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

First step is dynamically assigning columns required to the @sql variable. Then, concatenating the previously assigned @sql variable with the rest of the final SELECT query, then re-assign it to the @sql variable. The query will end up with:

SELECT MAX(CASE WHEN language="german" THEN text END) AS "german",
       MAX(CASE WHEN language="english" THEN text END) AS "english" 
FROM mytable;

Lastly, we prepare, execute then deallocate the statement assigned in the @sql variable and you'll get your expected result.

Demo fiddle