Using array and loop to update MySQL table-CodePudding

I have a table with 3 columns

pID | key_name   | value  
-----------------------
10 | 'series'   |'Songs'
10 | 'wood'     |'Beech' 
10 | 'language' |'German'
11 | 'series'   |'Songs'
11 | 'wood'     |'Oak'
11 | 'language' |'French'
12 | 'series'   |'Exams'
12 | 'language' |'English'

I need to update a table where the key_names are now column names, thus

pID | series | wood  | language
-----------------------------
10 ! 'Songs'|'Beech'|'German'
11 | 'Songs'|'Oak'  |'French'
12 | 'Exams'|       |'English'

Now I could write some SQL like

UPDATE dest-tbl INNER JOIN start-tbl
ON dest-tbl.pID = start-tbl.pID
SET dest-tbl.series = start-tbl.value
WHERE dest-tbl.key_name = 'series'

but since there are 65 different key_name values that would mean having to have 65 variations on that SQL.

It strikes me that the best way to do that might be to create an array of the key_name values and loop through that, except that I haven't go a clue how to do that.

Can anyone help me with this?

Using MariaDB v10.3

MTIA

CodePudding user response：

SELECT 
   t1.pID, 
   t1.value,
   t2.value,
   t3.value
FROM Table1 t1
LEFT JOIN Table1 t2 on t2.pID=t1.pID and t2.key_name='wood'
LEFT JOIN Table1 t3 on t3.pID=t1.pID and t3.key_name='language'
WHERE  t1.key_name='series';

output:

  --------   ----------   ----------   ----------  
| 10       | Songs      | Beech      | German     |
| 11       | Songs      | Oak        | French     |
| 12       | Exams      |            | English    |
  --------   ----------   ----------   ----------

DBFIDDLE

CodePudding user response：

As i said in the comments to get a flexible solution you need dynamic sql

CREATE TABLE tab1 (
  `pID` INTEGER,
  `key_name` VARCHAR(10),
  `value` VARCHAR(9)
);

INSERT INTO tab1
  (`pID`, `key_name`, `value`)
VALUES
  ('10', 'series', 'Songs'),
  ('10', 'wood', 'Beech'),
  ('10', 'language', 'German'),
  ('11', 'series', 'Songs'),
  ('11', 'wood', 'Oak'),
  ('11', 'language', 'French'),
  ('12', 'series', 'Exams'),
  ('12', 'language', 'English');

    SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'max(CASE WHEN `key_name` = ''',
          `key_name`,
          ''' THEN `value` ELSe "" END) AS `',
          
          `key_name`, '`'
        )
      ) INTO @sql
      
      FROM `tab1` ;

SET @sql = CONCAT('SELECT `pID`, ',@sql,' from tab1
GROUP BY `pID`');

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

✓

✓

pID | language | series | wood 
--: | :------- | :----- | :----
 10 | German   | Songs  | Beech
 11 | French   | Songs  | Oak  
 12 | English  | Exams  |      

✓

db<>fiddle here