Repeat rows in the result based on an integer value in column
0
I have following table.
Sales:
id quantity price_charged
------------------------------
101 2 100
102 3 300
103 1 120
I want to select the records such that it repeat Rows N time according to quantity column value.
So I need following results
id quantity price_charged
--------------------------------
101 1 50
101 1 50
102 1 100
102 1 100
102 1 100
103 1 120
mysql sql database
edited Nov 21 '18 at 12:59
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 12:56
VikVik
113
add a comment |
0
I have following table.
Sales:
id quantity price_charged
------------------------------
101 2 100
102 3 300
103 1 120
I want to select the records such that it repeat Rows N time according to quantity column value.
So I need following results
id quantity price_charged
--------------------------------
101 1 50
101 1 50
102 1 100
102 1 100
102 1 100
103 1 120
mysql sql database
edited Nov 21 '18 at 12:59
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 12:56
VikVik
113
Isn't this something you should do in the client?
– Joakim Danielson
Nov 21 '18 at 12:59
What is the purpose of this ? You can utilize number-generator tables.
– Madhur Bhaiya
Nov 21 '18 at 13:09
Looks like i found the solution using this approach only.
– Vik
Nov 23 '18 at 10:10
add a comment |
0
0
0
I have following table.
Sales:
id quantity price_charged
------------------------------
101 2 100
102 3 300
103 1 120
I want to select the records such that it repeat Rows N time according to quantity column value.
So I need following results
id quantity price_charged
--------------------------------
101 1 50
101 1 50
102 1 100
102 1 100
102 1 100
103 1 120
mysql sql database
edited Nov 21 '18 at 12:59
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 12:56
VikVik
113
I have following table.
Sales:
id quantity price_charged
------------------------------
101 2 100
102 3 300
103 1 120
I want to select the records such that it repeat Rows N time according to quantity column value.
So I need following results
id quantity price_charged
--------------------------------
101 1 50
101 1 50
102 1 100
102 1 100
102 1 100
103 1 120
mysql sql database
mysql sql database
edited Nov 21 '18 at 12:59
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 12:56
VikVik
113
edited Nov 21 '18 at 12:59
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 12:56
VikVik
113
edited Nov 21 '18 at 12:59
Madhur Bhaiya
19.6k62236
edited Nov 21 '18 at 12:59
Madhur Bhaiya
19.6k62236
edited Nov 21 '18 at 12:59
Madhur Bhaiya
19.6k62236
19.6k62236
asked Nov 21 '18 at 12:56
VikVik
113
asked Nov 21 '18 at 12:56
VikVik
113
asked Nov 21 '18 at 12:56
VikVik
113
113
Isn't this something you should do in the client?
– Joakim Danielson
Nov 21 '18 at 12:59
What is the purpose of this ? You can utilize number-generator tables.
– Madhur Bhaiya
Nov 21 '18 at 13:09
Looks like i found the solution using this approach only.
– Vik
Nov 23 '18 at 10:10
add a comment |
Isn't this something you should do in the client?
– Joakim Danielson
Nov 21 '18 at 12:59
What is the purpose of this ? You can utilize number-generator tables.
– Madhur Bhaiya
Nov 21 '18 at 13:09
Looks like i found the solution using this approach only.
– Vik
Nov 23 '18 at 10:10
Isn't this something you should do in the client?
– Joakim Danielson
Nov 21 '18 at 12:59
Isn't this something you should do in the client?
– Joakim Danielson
Nov 21 '18 at 12:59
What is the purpose of this ? You can utilize number-generator tables.
– Madhur Bhaiya
Nov 21 '18 at 13:09
What is the purpose of this ? You can utilize number-generator tables.
– Madhur Bhaiya
Nov 21 '18 at 13:09
Looks like i found the solution using this approach only.
– Vik
Nov 23 '18 at 10:10
Looks like i found the solution using this approach only.
– Vik
Nov 23 '18 at 10:10
add a comment |
2 Answers
2
active
oldest
votes
1
I think, it is better to resolve not with query(SQL).
There is some generation feature, but its performance is poor.
You have to change your model(store always 1 quantity), or process it in backend(java/c/stb.)
Select id,
1 as quantity,
price_charged
from table_name t
JOIN
(SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5) counter
ON (counter.n<=t.quantity)
The joined subquery reapeted numbers from 0 to 99999 it is the burn it maximum for quantity. The join repeat by the counter 0... quantity-1 values.
edited Nov 21 '18 at 13:23
Madhur Bhaiya
19.6k62236
answered Nov 21 '18 at 13:01
László TóthLászló Tóth
765
Please add explaination. also it is recommended to avoid using comma based joins and use modernJOIN ..ONsyntax instead. +1
– Madhur Bhaiya
Nov 21 '18 at 13:14
Thank you. I reedited it with join and some explaintattion
– László Tóth
Nov 21 '18 at 13:22
I don't think this solution would work, and I am not sure whether this is a generic solution or just one off. I can think of a solution having stored procedure but I wanted to see if I can have a solution with select query.
– Vik
Nov 21 '18 at 13:22
Perhaps a little bit better, if you insert counts into one table and join to that table. But it works if 99999>=quantity>=0.
– László Tóth
Nov 21 '18 at 13:25
add a comment |
0
I was able to come up with a solution for my problem after referring an answer for how to generate series in mysql. Here is the link.
SELECT
sal.id,
1 as quantity, sal.quantity as originalQty,
sal.price_charged/sal.quantity
FROM
(SELECT
@num := @num + 1 AS count
FROM
sales, -- this can be any table but it should have row count
-- more than what we expect the max value of Sales.quantity column
(SELECT @num := 0) num
LIMIT
100) ctr
JOIN sales sal
ON sal.quantity >= ctr.count
order by id;
answered Nov 23 '18 at 6:16
VikVik
113
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53412527%2frepeat-rows-in-the-result-based-on-an-integer-value-in-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
I think, it is better to resolve not with query(SQL).
There is some generation feature, but its performance is poor.
You have to change your model(store always 1 quantity), or process it in backend(java/c/stb.)
Select id,
1 as quantity,
price_charged
from table_name t
JOIN
(SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5) counter
ON (counter.n<=t.quantity)
The joined subquery reapeted numbers from 0 to 99999 it is the burn it maximum for quantity. The join repeat by the counter 0... quantity-1 values.
edited Nov 21 '18 at 13:23
Madhur Bhaiya
19.6k62236
answered Nov 21 '18 at 13:01
László TóthLászló Tóth
765
Please add explaination. also it is recommended to avoid using comma based joins and use modernJOIN ..ONsyntax instead. +1
– Madhur Bhaiya
Nov 21 '18 at 13:14
Thank you. I reedited it with join and some explaintattion
– László Tóth
Nov 21 '18 at 13:22
I don't think this solution would work, and I am not sure whether this is a generic solution or just one off. I can think of a solution having stored procedure but I wanted to see if I can have a solution with select query.
– Vik
Nov 21 '18 at 13:22
Perhaps a little bit better, if you insert counts into one table and join to that table. But it works if 99999>=quantity>=0.
– László Tóth
Nov 21 '18 at 13:25
add a comment |
1
I think, it is better to resolve not with query(SQL).
There is some generation feature, but its performance is poor.
You have to change your model(store always 1 quantity), or process it in backend(java/c/stb.)
Select id,
1 as quantity,
price_charged
from table_name t
JOIN
(SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5) counter
ON (counter.n<=t.quantity)
The joined subquery reapeted numbers from 0 to 99999 it is the burn it maximum for quantity. The join repeat by the counter 0... quantity-1 values.
edited Nov 21 '18 at 13:23
Madhur Bhaiya
19.6k62236
answered Nov 21 '18 at 13:01
László TóthLászló Tóth
765
Please add explaination. also it is recommended to avoid using comma based joins and use modernJOIN ..ONsyntax instead. +1
– Madhur Bhaiya
Nov 21 '18 at 13:14
Thank you. I reedited it with join and some explaintattion
– László Tóth
Nov 21 '18 at 13:22
I don't think this solution would work, and I am not sure whether this is a generic solution or just one off. I can think of a solution having stored procedure but I wanted to see if I can have a solution with select query.
– Vik
Nov 21 '18 at 13:22
Perhaps a little bit better, if you insert counts into one table and join to that table. But it works if 99999>=quantity>=0.
– László Tóth
Nov 21 '18 at 13:25
add a comment |
1
1
1
I think, it is better to resolve not with query(SQL).
There is some generation feature, but its performance is poor.
You have to change your model(store always 1 quantity), or process it in backend(java/c/stb.)
Select id,
1 as quantity,
price_charged
from table_name t
JOIN
(SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5) counter
ON (counter.n<=t.quantity)
The joined subquery reapeted numbers from 0 to 99999 it is the burn it maximum for quantity. The join repeat by the counter 0... quantity-1 values.
edited Nov 21 '18 at 13:23
Madhur Bhaiya
19.6k62236
answered Nov 21 '18 at 13:01
László TóthLászló Tóth
765
I think, it is better to resolve not with query(SQL).
There is some generation feature, but its performance is poor.
You have to change your model(store always 1 quantity), or process it in backend(java/c/stb.)
Select id,
1 as quantity,
price_charged
from table_name t
JOIN
(SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5) counter
ON (counter.n<=t.quantity)
The joined subquery reapeted numbers from 0 to 99999 it is the burn it maximum for quantity. The join repeat by the counter 0... quantity-1 values.
edited Nov 21 '18 at 13:23
Madhur Bhaiya
19.6k62236
answered Nov 21 '18 at 13:01
László TóthLászló Tóth
765
edited Nov 21 '18 at 13:23
Madhur Bhaiya
19.6k62236
edited Nov 21 '18 at 13:23
Madhur Bhaiya
19.6k62236
edited Nov 21 '18 at 13:23
Madhur Bhaiya
19.6k62236
19.6k62236
answered Nov 21 '18 at 13:01
László TóthLászló Tóth
765
answered Nov 21 '18 at 13:01
László TóthLászló Tóth
765
answered Nov 21 '18 at 13:01
László TóthLászló Tóth
765
765
Please add explaination. also it is recommended to avoid using comma based joins and use modernJOIN ..ONsyntax instead. +1
– Madhur Bhaiya
Nov 21 '18 at 13:14
Thank you. I reedited it with join and some explaintattion
– László Tóth
Nov 21 '18 at 13:22
I don't think this solution would work, and I am not sure whether this is a generic solution or just one off. I can think of a solution having stored procedure but I wanted to see if I can have a solution with select query.
– Vik
Nov 21 '18 at 13:22
Perhaps a little bit better, if you insert counts into one table and join to that table. But it works if 99999>=quantity>=0.
– László Tóth
Nov 21 '18 at 13:25
add a comment |
Please add explaination. also it is recommended to avoid using comma based joins and use modernJOIN ..ONsyntax instead. +1
– Madhur Bhaiya
Nov 21 '18 at 13:14
Thank you. I reedited it with join and some explaintattion
– László Tóth
Nov 21 '18 at 13:22
I don't think this solution would work, and I am not sure whether this is a generic solution or just one off. I can think of a solution having stored procedure but I wanted to see if I can have a solution with select query.
– Vik
Nov 21 '18 at 13:22
Perhaps a little bit better, if you insert counts into one table and join to that table. But it works if 99999>=quantity>=0.
– László Tóth
Nov 21 '18 at 13:25
Please add explaination. also it is recommended to avoid using comma based joins and use modern
JOIN ..ON syntax instead. +1– Madhur Bhaiya
Nov 21 '18 at 13:14
Please add explaination. also it is recommended to avoid using comma based joins and use modern
JOIN ..ON syntax instead. +1– Madhur Bhaiya
Nov 21 '18 at 13:14
Thank you. I reedited it with join and some explaintattion
– László Tóth
Nov 21 '18 at 13:22
Thank you. I reedited it with join and some explaintattion
– László Tóth
Nov 21 '18 at 13:22
I don't think this solution would work, and I am not sure whether this is a generic solution or just one off. I can think of a solution having stored procedure but I wanted to see if I can have a solution with select query.
– Vik
Nov 21 '18 at 13:22
I don't think this solution would work, and I am not sure whether this is a generic solution or just one off. I can think of a solution having stored procedure but I wanted to see if I can have a solution with select query.
– Vik
Nov 21 '18 at 13:22
Perhaps a little bit better, if you insert counts into one table and join to that table. But it works if 99999>=quantity>=0.
– László Tóth
Nov 21 '18 at 13:25
Perhaps a little bit better, if you insert counts into one table and join to that table. But it works if 99999>=quantity>=0.
– László Tóth
Nov 21 '18 at 13:25
add a comment |
0
I was able to come up with a solution for my problem after referring an answer for how to generate series in mysql. Here is the link.
SELECT
sal.id,
1 as quantity, sal.quantity as originalQty,
sal.price_charged/sal.quantity
FROM
(SELECT
@num := @num + 1 AS count
FROM
sales, -- this can be any table but it should have row count
-- more than what we expect the max value of Sales.quantity column
(SELECT @num := 0) num
LIMIT
100) ctr
JOIN sales sal
ON sal.quantity >= ctr.count
order by id;
answered Nov 23 '18 at 6:16
VikVik
113
add a comment |
0
I was able to come up with a solution for my problem after referring an answer for how to generate series in mysql. Here is the link.
SELECT
sal.id,
1 as quantity, sal.quantity as originalQty,
sal.price_charged/sal.quantity
FROM
(SELECT
@num := @num + 1 AS count
FROM
sales, -- this can be any table but it should have row count
-- more than what we expect the max value of Sales.quantity column
(SELECT @num := 0) num
LIMIT
100) ctr
JOIN sales sal
ON sal.quantity >= ctr.count
order by id;
answered Nov 23 '18 at 6:16
VikVik
113
add a comment |
0
0
0
I was able to come up with a solution for my problem after referring an answer for how to generate series in mysql. Here is the link.
SELECT
sal.id,
1 as quantity, sal.quantity as originalQty,
sal.price_charged/sal.quantity
FROM
(SELECT
@num := @num + 1 AS count
FROM
sales, -- this can be any table but it should have row count
-- more than what we expect the max value of Sales.quantity column
(SELECT @num := 0) num
LIMIT
100) ctr
JOIN sales sal
ON sal.quantity >= ctr.count
order by id;
answered Nov 23 '18 at 6:16
VikVik
113
I was able to come up with a solution for my problem after referring an answer for how to generate series in mysql. Here is the link.
SELECT
sal.id,
1 as quantity, sal.quantity as originalQty,
sal.price_charged/sal.quantity
FROM
(SELECT
@num := @num + 1 AS count
FROM
sales, -- this can be any table but it should have row count
-- more than what we expect the max value of Sales.quantity column
(SELECT @num := 0) num
LIMIT
100) ctr
JOIN sales sal
ON sal.quantity >= ctr.count
order by id;
answered Nov 23 '18 at 6:16
VikVik
113
answered Nov 23 '18 at 6:16
VikVik
113
answered Nov 23 '18 at 6:16
VikVik
113
answered Nov 23 '18 at 6:16
VikVik
113
113
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53412527%2frepeat-rows-in-the-result-based-on-an-integer-value-in-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Isn't this something you should do in the client?
– Joakim Danielson
Nov 21 '18 at 12:59
What is the purpose of this ? You can utilize number-generator tables.
– Madhur Bhaiya
Nov 21 '18 at 13:09
Looks like i found the solution using this approach only.
– Vik
Nov 23 '18 at 10:10