MySQL- Create Duplicate Results in SELECT statement

Question

I have a table like

ColumnA	ColumnB	ColumnC
ABC	TextA	3
EFG	TextB	1
EFG	TextC	2

How do I write a SELECT statement that will return the # of rows for ColumnA and ColumnB based on the values of ColumnC

Expect results:

ColumnA	ColumnB
ABC	TextA
ABC	TextA
ABC	TextA
EFG	TextB
EFG	TextC
EFG	TextC

what does select version(); show? and is there some expected maximum value of column c? — ysth
– ysth, Commented Oct 13, 2021 at 2:25
Maximum value of ColumnC is less than 100, total volume is not huge so performance is not critical — John
– John, Commented Oct 13, 2021 at 2:25

Ajax1234 · Accepted Answer · 2021-10-13 02:26:10Z

1

You can use a recursive cte:

with recursive cte(a, b, c) as (
    select cola, colb, colc - 1 from t
    union all
    select a, b, c - 1 from cte where c > 0
)
select a, b from cte order by a;

answered Oct 13, 2021 at 2:26

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL- Create Duplicate Results in SELECT statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related