SQL. How to convert rows to columns?

Question

SQL

I have a table like this:


CREATE TABLE new_t(id INT, type_data TEXT, amount INT, year INT, PRIMARY KEY(id));

INSERT INTO new_t VALUES(1, 'First', 500, 2018);
INSERT INTO new_t VALUES(2, 'Second', 90, 2018);
INSERT INTO new_t VALUES(3, 'First', 300, 2019);
INSERT INTO new_t VALUES(4, 'Second', 250, 2019);
INSERT INTO new_t VALUES(5, 'First', 600, 2020);
INSERT INTO new_t VALUES(6, 'Second', 500, 2020);

From it it is necessary to make a table of the form.


This must be done in one query without creating a new table.
Those. I need to select unique values ​​from the last column via SELECT and make them new columns

. I am looking for unique values ​​via ORDER BY query:
SELECT DISTINCT year FROM new_t ORDER BY year ASC

But I am getting rows, not columns. If I do a second SELECT, then the result is not at all what I want to see:
SELECT type_data, (SELECT DISTINCT year FROM new_t ORDER BY year ASC) FROM new_t GROUP BY type_data I looked through

all the forums and I can’t figure out how to make a query that transforms rows into columns. The values ​​in the columns can be of any number, so it is important not to depend on the current values, but to get their number through SELECT.

What am I doing wrong?

Answer 1

There are PIVOT and UNPIVOT for such things.
But since the number of columns is dynamic, they cannot do that in their pure form, so I would dynamically form the part of the query where the list of columns is (PHP, C #, T-SQL, etc. ... in short what you use in addition to pure queries)
https://www.sqlshack.com/dynamic-pivot-tables-in-s...
PS In general, it would be nice to add the name of the database to the tags: MSSQL, MYSQL, Oracle, etc.

Answer 2

Here is how it can be done, starting with the words: "But what happens if you don't know the dates ahead of time...".