In Mysql, batch add multiple columns to table based on another column

Question

I have a table with three columns. For each id, we have up to 400 index values. I want to add columns based on the number of index. In the example I provided, I have 4 indexes and then I added four columns to the table. This is the table I have:

Create table buy_sell (id int, idx varchar(255), sell float(2, 1));

insert into buy_sell (id, idx, sell) values ​​('1', 'a', '4');
insert into buy_sell (id, idx, sell) values ​​('1', 'b', '6');
insert into buy_sell (id, idx, sell) values ​​('1', 'c', '8');
insert into buy_sell (id, idx, sell) values ​​('1', 'd', '9');
insert into buy_sell (id, idx, sell) values ​​('3', 'b ', '1');
insert into buy_sell (id, idx, sell) values ​​('3', 'c ', '2');
insert into buy_sell (id, idx, sell) values ​​('2', 'a', '5');
insert into buy_sell (id, idx, sell) values ​​('2', 'b', '7');
insert into buy_sell (id, idx, sell) values ​​('2', 'd', '5');
SELECT * FROM buy_sell;

This is the result:

id	idx	For Sale
1	a	4.0
1	b	6.0
1	c	8.0
1	d	9.0
3	b	1.0
3	c	2.0
2	a	5.0
2	b	7.0
2	d	5.0

For example, for id=1, we have four indexes here (a, b, c, d), and then we have four non-zero columns.For id = 3 we have two indexes (b, c) and then we have two non-zero columns, so for the first column we put zero, for the second column we put 1 and for the third column we Place 2. So on and so forth. This is the table I want:

id	Sell 1	Sell 2	Sell 3	Sell 4
1	4	6	8	9
3	0	1	2	0
2	5	7	0	5

I searched a lot and tried Group_concat, JSON_ARRAYAGG etc. but I couldn't find a solution. what do I need to do?

Answer 1

The SQL language has very strict requirements for knowing the number of columns in the result at query compile time, before any data is viewed. If you have to look at the data to determine the number of columns you want, then you can only use (potentially dangerous) dynamic SQL, which requires three steps:

1. Run a query to find information about the required columns.
2. Dynamically construct a new SQL statement using the results of step 1.
3. Run the SQL in step 2.

In this case, you don't know how many columns are required, only that it is "up to 400 columns". With this in mind, you might see code like this:

SELECT ID, 
     MAX(CASE WHEN IDX = 'a' THEN sell ELSE 0 END) as sell1, 
     MAX(CASE WHEN IDX = 'b' THEN sell ELSE 0 END) as sell2, 
     MAX(CASE WHEN IDX = 'c' THEN sell ELSE 0 END) as sell3,
     -- ... 
     MAX(CASE WHEN IDX = '??' THEN sell ELSE 0 END) as sell400
FROM `buy_sell`
GROUP BY ID

Yes, you do need to specify something in the query for every possible column. This also assumes that your sell values ​​are all greater than 0. If your values ​​are likely to be a mix of positive and negative numbers, you might try using SUM() instead of MAX().

This approach is also directly contrary to the set theory principles behind relational databases, so in practice you are better off letting client code or reporting tools do the pivoting on the data.