0

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:

id idx sell
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

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:

id sell1 sell2 sell3 sell4
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 can't find out how to solve this. What do I need to do?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • 1
    You should check https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql – Ergest Basha Feb 28 '22 at 20:35
  • @ergestBasha Thanks. However, in the provided link, there is a limited index and it is easy to use the when case. Here I have 400 index. –  Feb 28 '22 at 20:48
  • See how I edited the question to use formatted tables instead of screenshots? Please understand that **screenshots of data area really bad here.** Please do it this way from now on. Using copy/paste tools it shouldn't take any longer than posting the screenshot, and will make it much more likely you get a fast and accurate answer. – Joel Coehoorn Feb 28 '22 at 20:54
  • @joelCoehoorn Thanks you so much. Yes. now is very clear. I will do that. –  Feb 28 '22 at 20:55
  • @Cheese -- as you can see you are getting the same answers I gave you in your prior question. My point is still the same -- if this is really what your data looks like what is the application for a table with 400 columns? How exactly are you going to use this result? There is probably a better way to do it if we know the answer to that question. – Hogan Feb 28 '22 at 20:55
  • @hogan: for each id, I have 3 features. The other two features are provided in the tables with 400 columns. So, I want to build the same table as the other two feature. I am new to this area and with high probablity, I am doing a non-efficient way. –  Feb 28 '22 at 20:59
  • @Cheese -- I don't understand, it is not 3 features if there are 400 of them. Sounds like 402 features to me. – Hogan Feb 28 '22 at 21:02
  • @Hogan No. Each feature has 400 column. This table is only for one feature. –  Feb 28 '22 at 21:07
  • in any case here is an example of how to write dynamic SQL which is the only way to solve this problem as presented -- https://stackoverflow.com/a/11406571/215752 (this is 12 years old, so don't expect SQL to change to make doing this easier.) – Hogan Feb 28 '22 at 21:08

3 Answers3

1

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.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
0

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;
Kathi Kellenberger
  • 104
  • 4
0

Here we have a solution which is basic SQL, which adjusts for all columns thanks to a sub-query SELECT DISTINCT. The alignment of columns is dependent on the format of the values but we can further control that with something like

LEFT(CONCAT('00',sell),6)

if bigger numbers are involved.

select 
  i.idx,
  group_concat(coalesce(
  bs.sell,'   ')
  order by s.sell 
  separator '|'  ) 
  sells 
from
  (select distinct sell
  from buy_sell order by sell)s
cross join
  (select distinct idx 
  from buy_sell)i
left join buy_sell bs
on s.sell=bs.sell
and i.idx=bs.idx
group by i.idx
order by i.idx;

idx | sells                          
:-- | :------------------------------
a   |    |   |4.0|5.0|   |   |   |   
b   | 1.0|   |   |   |6.0|7.0|   |   
c   |    |2.0|   |   |   |   |8.0|   
d   |    |   |   |5.0|   |   |   |9.0

db<>fiddle here