动态列名称取决于查询结果的单行

Question

I need some help with dynamical column names in a sql query. First I will try to explain my database structure and then the problem.

Database structure:

admin_group table:

+--------+----------------+
| id     | language_code  |
+--------+----------------+
| 1      | en_UK          | 
| 2      | de_DE          | 
| 3      | es_ES          |
+--------+----------------+

constructions_meta table:

+--------+-----------------+----------+
| id     | admin_group_FK  | value    |
+--------+-----------------+----------+
| 1      | 1               | 0.13     |
| 2      | 2               | 0.12     |
| 3      | 3               | 0.10     |
+--------+-----------------+----------+

construction_lang table:

+--------+-----------------+----------------+----------------+
| id     | en_UK_name      | de_DE_name     |es_ES_name      |
+--------+-----------------+----------------+----------------+
| 1      | Construction 1  | Konstruktion 1 | Construcción 1 |       
| 2      | Construction 2  | Konstruktion 2 | Construcción 2 |  
| 3      | Construction 3  | Konstruktion 3 | Construcción 3 |  
+--------+-----------------+----------------+----------------+

Those are my tables in the database. What I need here is to get the names of the constructions regarding the language code for each construction. For example I want to list the constructions as following:

1. Construction 1
2. Konstruktion 2
3. Construcción 3

Answer 1

It's probably easiest to do that in two passes. That'll give you the ability to expand the width of the construction_lang table without affecting the SQL statements significantly.

Firstly you issue a SQL statement to get the column names that you need, then you use that result set to build a second SQL statement that will get the names for you.

This is not the ideal solution, as it is working around the data-model.

Given a query that returns back the id and language from admin_group in line with:

array( array( 'id' => 1, 'language_code' => 'en_UK' ),
       array( 'id' => 2, 'language_code' => 'de_DE' ),
       array( 'id' => 3, 'language_code' => 'es_ES' ) )

You can build a statement with something along the lines of (using the other answer from diarmuid as an example)

$sql = "select c.id , (select case c.admin_group_FK
";
foreach( $languages as $thisLanguage ) {
   $sql .= "when {$thisLanguage['id']} then l.${thisLanguage['language_code']}_name
";
}
$sql .= ...

This is a round-about way of doing it, because of the data-model you have. Ideally you wouldn't need the "dynamic" SQL."

If you want to remove the round-about-ness, and build something more in line with standard relational database theory, then you can change the model so that the construction_lang table is more like this:

+------------------------+-----------------+----------------+
| constructions_meta_fk  | language        | name           |
+------------------------+-----------------+----------------+
| 1                      | en_UK           | Construction 1 |
| 1                      | de_DE           | Konstruktion 1 |
| 1                      | es_ES           | Construcción 1 |
| 2                      | en_UK           | Construction 2 |
| 2                      | de_DE           | Konstruktion 2 |
| 2                      | es_ES           | Construcción 2 |
| 3                      | en_UK           | Construction 3 |
| 3                      | de_DE           | Konstruktion 3 |
| 3                      | es_ES           | Construcción 3 |
+------------------------+-----------------+----------------+