SQL Timeline Query

Question

read already some post but was not able to find a solution yet.

I gota table which looks like this:

and I would like to transform this data, so that I got a line (or row) per ID and an entry per date which displays the Status. The value column does not change its value for the corresponding id.

or

I am currently not able to do it. Even without the value row/line.

CREATE TABLE test (
  id INT,
  date1 text,
  status1 INT,
  value1 INT
);
INSERT INTO test VALUES (1, '01.01.2022', 1, 60);
INSERT INTO test VALUES (2, '01.01.2022', 1, 30);
INSERT INTO test VALUES (3, '01.01.2022', 7, 90);
INSERT INTO test VALUES (1, '02.01.2022', 7, 60);
INSERT INTO test VALUES (2, '02.01.2022', 7, 30);
INSERT INTO test VALUES (3, '02.01.2022', 3, 90);
INSERT INTO test VALUES (1, '03.01.2022', 7, 60);
INSERT INTO test VALUES (2, '03.01.2022', 5, 30);
INSERT INTO test VALUES (3, '03.01.2022', 7, 90);

Based on your suggestions I tried:

SELECT *
FROM
(
    SELECT id, value1
    FROM test
) AS SourceTable 
PIVOT(AVG(status1) FOR date1 IN(select DISTINCT date1
                           from test)) AS PivotTable;  

But I can not find my error.

Answer 1

Schema (MySQL v8.0)

CREATE TABLE test (
  id INT,
  date text,
  status INT,
  value INT
);
INSERT INTO test VALUES (1, '01.01.2022', 1, 60);
INSERT INTO test VALUES (2, '01.01.2022', 1, 30);
INSERT INTO test VALUES (3, '01.01.2022', 7, 90);
INSERT INTO test VALUES (1, '02.01.2022', 7, 60);
INSERT INTO test VALUES (2, '02.01.2022', 7, 30);
INSERT INTO test VALUES (3, '02.01.2022', 3, 90);
INSERT INTO test VALUES (1, '03.01.2022', 7, 60);
INSERT INTO test VALUES (2, '03.01.2022', 5, 30);
INSERT INTO test VALUES (3, '03.01.2022', 7, 90);

---

Query #1

SELECT
ID,
MAX(VALUE) AS VALUE, 
sum(CASE WHEN date = '01.01.2022' THEN status ELSE 0 END) AS '01.01.2022',
sum(CASE WHEN date = '02.01.2022' THEN status ELSE 0 END) AS '02.01.2022',
sum(CASE WHEN date = '03.01.2022' THEN status ELSE 0 END) AS '03.01.2022'
FROM test
GROUP BY ID;

ID	VALUE	01.01.2022	02.01.2022	03.01.2022
1	60	1	7	7
2	30	1	7	5
3	90	7	3	7

---

View on DB Fiddle