I have a table with three columns. For each id we have as many as 400 index values. I want to add columns based on the number of index. In the example which I provide, I have 4 index, then I add four columns to the table. Here 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;

Here is the result:

So, for example for id=1, we have four index here (a, b, c, d) and then we have four non-zero columns. For id = 3, we have two index (b, c), then we have two non-zero columns, so for column 1, we put zero, for column 2 we put the 1, and for column 3 we put the 2. And so on. Here is the table that I want:

I searched a lot, and tried Group_concat, JSON_ARRAYAGG, etc, but I can't find out how to solve this. What do I need to do?

CodePudding user response：

The SQL language has a very strict requirement for you to know the number of columns in the results at query compile time, before looking at any data. If you have to look at data to find how many columns you want, you're stuck using (potentially dangerous) dynamic SQL, over three steps:

1. Run a query to find information about the desired columns.
2. Build a new SQL statement on the fly using the results from step 1.
3. Run the SQL from step 2.

In this case, you don't know how many columns you need, except that it's "Up to 400". With that in mind, you're looking at something 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 really do need to specify something in the query for every potential column. This also assumes all your sell values are greater than 0. If you could have a mix of positive and negative values you can try SUM() instead of MAX().

This kind of thing is also in direct opposition to the Set Theory principles behind relational databases, such that in practice you're generally much better off letting your client code or reporting tool pivot the data anyway.

CodePudding user response：

Try this, but your inserts for 3 have extra spaces, so fix that. And, I just noticed that you could have 400 indexes, so probably not a good approach.

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 = 'd' then sell else 0 end ) as Sell4
from buy_sell
GROUP BY ID;