Dynamically transpose rows in SQLite without using extensions

Question

I am having a problem creating a query in SQLite.

I have two SQL tables, related by an ID, and I would like to transpose some of the rows after joining them.

Since I cannot share information from the real database, I have created a toy database to illustrate better what I want to achieve. Here, I have a MAINTABLE, containing its own ids and more stuff, and a SECONDARYTABLE, containing its own ids, references to MAINTABLE, and key/value pairs.

MAINTABLE
idMainTable  MoreStuff
1            asdf
2            fdsa
3            hjkl
4            lkhj

SECONDARY TABLE
idSecondaryTable  idMainTable  key    value
1                 1            Key1       a
2                 1            Key5       s
3                 1            Key7       d
4                 1            Key8       f
5                 2            Key1       g
6                 2            Key4       h
7                 2            Key25      j
8                 3            Key2       l
9                 3            Key6       z
10                4            Key7       y

What I would like to do here, is a query able to join those two tables, and transpose the key and value rows as columns like this, so the keys are columns in the resulting table:

EXPECTED TABLE
idMainTable  MoreStuff   Key1  Key2  Key4  Key5  Key6  Key7  Key8 Key25
1                 asdf      a  null  null     s  null     d     f  null
2                 fdsa      g  null     h  null  null  null  null     j
3                 hjkl   null     l  null  null     z  null  null  null
4                 lkhj   null  null  null  null  null     y  null  null

I don't mind if the keys are ordered, or if the empty cells are shown as null, or as an empty cell.

I know from this link that, when the names of the different keys are known, conditional aggregation can be applied here. However, I cannot know the number of keys or the possible names of the keys, that is why I am looking for a dynamic solution. In this link a SQLite extension called pivot_vtab is also proposed, but the use of extensions is a restriction in my project and I cannot use it.

In MySQL, there is the option of using GROUP_CONCAT for this. I tried it in MySQL and it works. However, I've been trying a similar approach in SQLite but I cannot make this work.

This is the query working in MySQL, giving the desired result:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(keyColumn = ''',
      keyColumn,
      ''', value, NULL)) AS ',
      keyColumn
    )
  ) INTO @sql
FROM (MainTable INNER JOIN SecondaryTable ON MainTable.idMainTable =             
SecondaryTable.idMainTable);

SET @sql = CONCAT("SELECT SecondaryTable.idMainTable, ", @sql, 
            " FROM (MainTable INNER JOIN SecondaryTable ON MainTable.idMainTable = 
SecondaryTable.idMainTable)
            GROUP BY SecondaryTable.idMainTable");

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

And this is the code to create the toy database in SQLite:

PRAGMA foreign_keys = ON;
BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS `MainTable` (
  `idMainTable` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `MoreStuff` VARCHAR(45) NOT NULL,
  UNIQUE (`idMainTable` ASC));

CREATE TABLE IF NOT EXISTS `SecondaryTable` (
  `idSecondaryTable` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `idMainTable` INTEGER NOT NULL,
  `keyColumn` VARCHAR(45) NOT NULL,
  `value` VARCHAR(45) NOT NULL,
  UNIQUE (`idSecondaryTable` ASC),
  CONSTRAINT `fk_SecondaryTable_1`
    FOREIGN KEY (`idMainTable`)
    REFERENCES `MainTable` (`idMainTable`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

COMMIT;

BEGIN TRANSACTION;
INSERT INTO `MainTable` (`MoreStuff`) VALUES ('asdf');
INSERT INTO `MainTable` (`MoreStuff`) VALUES ('fdsa');
INSERT INTO `MainTable` (`MoreStuff`) VALUES ('hjkl');
INSERT INTO `MainTable` (`MoreStuff`) VALUES ('lkhj');

INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (1, 'Key1', 'a');
INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (1, 'Key5', 's');
INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (1, 'Key7', 'd');
INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (1, 'Key8', 'f');
INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (2, 'Key1', 'g');
INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (2, 'Key4', 'h');
INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (2, 'Key25', 'j');
INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (3, 'Key2', 'l');
INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (3, 'Key6', 'z');
INSERT INTO `SecondaryTable` (`idMainTable`, `keyColumn`, `value`) VALUES (4, 'Key7', 'y');

COMMIT;

For testing purposes, I am using the following online SQLite IDE to generate the desired query.

Is there a way of achieving what I described using SQLite, without extensions?

If you know beforehand the columns you want to transpose you can achieve it easily, but if you want it dynamically unfortunately not with SQLite only. You will need to resort into a two step with whatever programming language you are using plus sqlite. — Jorge Campos, Nov 18 '21 at 18:36
I understand. I will use the programming language I am using then to generate the new table. Thank you to both of you! — Alberto Casas Ortiz, Nov 18 '21 at 18:56