Dynamically transpose rows in SQLite without using extensions-CodePudding

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?

CodePudding user response：

Looks like there is no way of achieve this using pure SQLite as it was shown in the comments.

As suggested in the comments, it is possible to construct the dynamic query using another programming language, as in my case, C .

The following links also confirm this:

StackExchange: Is it possible to create Dynamic SQL statements purely in SQLite?

StackExchange: How to pivot data in SQLite

StackOverflow: SQLITE transpose large number of rows into columns

If this changes in the future, I will accept other answers, or update this one if I find out by myself.