sql - Pivoting SQLite With Variable Data

Question

I have the following tables in SQLite.

CREATE TABLE IF NOT EXISTS "payouts" (
"id" INTEGER NOT NULL UNIQUE,
"amount" TEXT NOT NULL);

CREATE TABLE "orders" (
"id" INTEGER NOT NULL UNIQUE,
"number" TEXT NOT NULL UNIQUE,
"amount" TEXT NOT NULL,
"fees" REAL NOT NULL,
"payout_id" INTEGER NOT NULL);

CREATE TABLE "tax" (
"rate" TEXT NOT NULL,
"amount" REAL NOT NULL,
"tax" REAL NOT NULL,
"order_id"  INTEGER NOT NULL);

INSERT INTO payouts VALUES (1, 97.43)

INSERT INTO orders VALUES (1, "01", 30, 5, 1)
INSERT INTO orders VALUES (2, "02", 80, 5, 1)

INSERT INTO tax VALUES (0.1, 20, 1.82, 1)
INSERT INTO tax VALUES (0.15, 10, 1.30, 1)
INSERT INTO tax VALUES (0.1, 50, 4.55, 2)
INSERT INTO tax VALUES (0.2, 30, 5.00, 2)

I would like a query which returns the following table where the rates are variable and new rates can be added at any time (so the columns of this table change depending on the input).

payout_id	amount_0.1	amount_0.15	amount_0.2	fees	payout_amount
1	70	10	30	12.57	97.43

question from:https://stackoverflow.com/questions/65843880/pivoting-sqlite-with-variable-data

Answer 1

Waitting for answers