Repeat row based on values in column

Question

Esteemed StackOverflow Community,

I have a table with the following data. I want to be able to produce n rows based on 5 - value in column E. Also want to be able to make the value in column F zero, keeping the values for A, B, C, D intact.

I'm not in a position to create a temp table.

INPUT

Table1

A    B    C    D    E    F
AA   BB   CC   DD   1    100
AA   BB   CC   DD   3    200
AA   BB   CC   DD   5    300
EE   FF   GG   HH   1    600

Table2

key      desc
1        AABBCCDD
2        EEFFGGHHH

OUTPUT

A    B    C    D    E    F    key
AA   BB   CC   DD   1    100  1
AA   BB   CC   DD   3    200  1
AA   BB   CC   DD   5    300  1
AA   BB   CC   DD   2    0    1
AA   BB   CC   DD   4    0    1
EE   FF   GG   HH   1    600  2
EE   FF   GG   HH   2    0    2
EE   FF   GG   HH   3    0    2
EE   FF   GG   HH   4    0    2
EE   FF   GG   HH   5    0    2

My existing SQL looks like this.

SELECT A.*,B.key from table1 as A
JOIN table2 as B on concat(A.A,A.B,A.C) = B.desc;

Note that desc is a reserved word. Also, this kind of problem is symptomatic of poor design — Strawberry, Dec 01 '18 at 14:30
Can you kindly explain how you get values for Column F .What is the business rules — saravanatn, Dec 01 '18 at 14:34
So you want to use (unseparated) values in table2 to connect to table1? How should MySQL know how the handle (parse) `AABBCCDD` into `AA`, `BB`, `CC`, `DD`.. Or is the possible data width within the table1 always 2 like in `CHAR(2)`? — Raymond Nijland, Dec 01 '18 at 14:37
@Strawberry - the column in `table2` is called `key_desc`. My bad for making it not clear. — Tydarius Jaxson, Dec 01 '18 at 22:58

LukStorms · Answer 1 · 2018-12-01T15:51:53.857

You'll need to cross join to a list of numbers somehow.

One way to that is using a tally table with enough numbers.

Then join those numbers to all the unique A, B, C, D.
This will get a basis for all possible combinations.

Then LEFT JOIN the tables to that result.

Test on SQL Fiddle here

SELECT 
 Abcd.A, Abcd.B, Abcd.C, Abcd.D, 
 Nums.n as E, 
 COALESCE(t1.F, 0) as F, 
 t2.`key`
FROM (
  SELECT MIN(E) AS MinE, MAX(E) AS MaxE, MAX(F) AS MaxF FROM Table1
) AS Limits
JOIN Nums ON n BETWEEN Limits.MinE AND Limits.MaxE
JOIN (
  SELECT DISTINCT A, B, C, D FROM Table1
) AS Abcd
LEFT JOIN Table1 AS t1 ON (t1.A, t1.B, t1.C, t1.D) = (Abcd.A, Abcd.B, Abcd.C, Abcd.D) AND t1.E = Nums.n
LEFT JOIN Table2 AS t2 ON t2.`desc` LIKE CONCAT(Abcd.A, Abcd.B, Abcd.C, Abcd.D, '%')
ORDER BY Abcd.A, Abcd.B, Abcd.C, Abcd.D, COALESCE(t1.F, Limits.MaxF), Nums.n;

Sample data used:

create table Table1 (A varchar(2), B varchar(2), C varchar(2), D varchar(2), E int, F int); 

create table Table2 (`key` int primary key auto_increment, `desc` varchar(30));

insert into Table1 (A, B, C, D, E, F) values
('AA','BB','CC','DD',1,100),
('AA','BB','CC','DD',3,200),
('AA','BB','CC','DD',5,300),
('EE','FF','GG','HH',1,600);

insert into Table2 (`desc`) values
('AABBCCDD'),
('EEFFGGHH');

 CREATE TABLE Nums (n int primary key);

 INSERT INTO Nums (n) VALUES
 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

this is brilliant, except that I cant create a table. Is there a version without creating the `nums` table? — Tydarius Jaxson, Dec 01 '18 at 22:36
@TydariusJaxson Well, if you're using MySql 8 then you could use a Recursive CTE I guess. But in MySql 5.x you don't have CTE's. But it's not the first time in SO that someone asked that. See [here](https://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql) for example. I find the `(select 1 as n union all select 2 union all select 3)` a bit wordy for my taste, but it works for a small list of numbers. But can you create a [TEMPORARY](https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html) table? — LukStorms, Dec 01 '18 at 22:45
I wouldn't mind doing the `Select 1 as n union select 2` to get the nums table. Question is, where would this go into your query? — Tydarius Jaxson, Dec 01 '18 at 23:31
@TydariusJaxson As a join on a sub-query to replace the join on Nums. `... JOIN () AS Nums ON ...` — LukStorms, Dec 01 '18 at 23:59

Repeat row based on values in column

1 Answers1