CODEWITHSUNDEEP
mysqlsql
user18155303
user18155303

Reputation:

Add multiple columns to the table based on the another column in Mysql

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?

Upvotes: 0

Views: 1342

Answers (3)

user18098820
user18098820

Reputation:

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

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

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.

Upvotes: 1

Kathi Kellenberger
Kathi Kellenberger

Reputation: 104

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;

Upvotes: 0

Related Questions