How can I implement an SQL query that starting from a query returning multiple records return a single records with multiple columns?

Question

I am not so into database and I have the following problem working on a MySql database.

I have a table like this:

CREATE TABLE CommodityName (
  id                   BigInt(20) NOT NULL AUTO_INCREMENT,
  commodity_details_id BigInt(20) NOT NULL,
  language_id          BigInt(20) NOT NULL,
  commodity_name       VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  description          Text CHARACTER SET latin1 COLLATE latin1_swedish_ci, 
  PRIMARY KEY (
      id
  )
) ENGINE=InnoDB AUTO_INCREMENT=87 ROW_FORMAT=DYNAMIC DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE CommodityName COMMENT = '';

The records in this table represents commodities information in different languages.

The commodity is specified by the univocally by the commodity_details_id (that is a FK of another table).

A specific commodity can have multiple record into this CommodityName table (I have a record for each translation in a specific language, represented by the language_id field). For example I have these 2 records:

id                   commodity_details_id language_id          commodity_name          description                                                                                                                                                                                                                                                    
-------------------------------------------------------------------------------------------------------
1                    1                    1                    Rice Asia               Rice Asia                                                                                                                                                                                                                                                      
16                   1                    3                    Umuceli Asia            Umuceli Asia

that represents the same commodities information (both having commodity_details_id=1) but in 2 languages (English and Kinirwanda language). In theory I can have also other languages, so I can have n records for a specific commodity.

I need to create a query where for a specified commodity (WHERE commodity_details_id=1) it returns all the commodity_name fields of the retrieved record as column of a single record.

Referring to the previous example something like this:

commodity_details_id       commodity_name_EN      commodity_name_RWA          commodity_name_FR   
----------------------------------------------------------------------------------------------------------------
1                          Rice Asia              Umuceli Asia                null

How can I implement a query like this that from multiple records transform a part of the information in each records as a new column in the new query?

Possible duplicate of [Is it possible to use Crosstab/Pivot Query in MySQL?](https://stackoverflow.com/questions/8920626/is-it-possible-to-use-crosstab-pivot-query-in-mysql) — symcbean, May 02 '18 at 10:34
Do you have a languages table as well? And do you want to have a column for every possible language in your language table or only those languages which appear in the commodityname table — P.Salmon, May 02 '18 at 10:54
The data-analysis jargon for your requirement is **pivoting** your table, for what it's worth. — O. Jones, May 02 '18 at 10:59
@P.Salmon yes the language_id field contains an FK to a Language table that is a typoligical table containing the allowed language in the system — AndreaNobili, May 02 '18 at 12:14
My comment about the word *pivoting* is intended to give you another way of searching for solutions. — O. Jones, May 02 '18 at 15:31

Rahul Jain · Answer 1 · 2018-05-02T17:59:20.800

0

Try This:

SELECT commodity_details_id,
MAX(CASE WHEN language_id=1 then commodity_name else null END) commodity_name_EN,
MAX(CASE WHEN language_id=3 then commodity_name else null END) commodity_name_RWA,
MAX(CASE WHEN language_id=2 then commodity_name else null END) commodity_name_FR
FROM CommodityName
group by commodity_details_id;

edited May 02 '18 at 17:59

answered May 02 '18 at 10:36

Rahul Jain

1,319
7
16

It is not working, I am obtaining this error message: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') commodity_name_EN, MAX(CASE WHEN language_id=3 then commodity_name else null)' at line 2 – AndreaNobili May 02 '18 at 12:19
... and `SELECT`s need `FROM`s. – O. Jones May 02 '18 at 15:32
It does not work in case of more than 3 languages assigned to same commodity_details_id. Hence It is static. – Udit Solanki May 02 '18 at 19:20

Udit Solanki · Answer 2 · 2018-05-02T19:29:26.293

What you are looking for is called Dynamic Pivot.

Look at the below code and see if it works for you. Schema might be little different in your case(just names of columns and tables). I am using commodityName, commodity_details, languages table. All you need to do is pass a commodity_details_id in parameter at 2nd line:

SET @sql = NULL;
SET @commodity_details_id = 1;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN language_id = ''',
      ID,
      ''' then ''',
      (select commodity_name from commodityname where language_id = L.Id and commodity_details_id = @commodity_details_id),
      ''' end) AS ',
      Concat('Commodity_Name','_', language_name)
    )
  ) INTO @sql
from languages L;

SET @sql = CONCAT(
'SELECT commodity_details_id, '
, @sql, 
' 
FROM CommodityName
where commodity_details_id = ',@commodity_details_id,'
group by commodity_details_id;
');


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

You can also create a stored routine out of this to call it from application or something.

Please comment if it doesn't work for you.

How can I implement an SQL query that starting from a query returning multiple records return a single records with multiple columns?

2 Answers2