Duplicate a row depending on some column value ?

Question

Say i have a table 'users', with 'user_id', 'name' and 'multiplier'.

If I do a basic select, I get one row for each user:

SELECT * FROM users

What I would like to do now, is to get mutiplier rows for each user. So if I have say two users in my table which those values

(1, 'user1', 1)
(2, 'user2', 6)

What I would like to get is a select that would return me 7 rows (1 x user1 and 6 x user2)

I realize that this is very easy to do on the client side, but this will be part of a much larger query and I can't afford to send the thousands of rows affected on the client to then multiply and sort and every other things I'm doing with it next ...

Thanks to anyone that can help

Answer 1

Could you do it with a temporary table? Build a copy of your users table with the correct number of rows on the client side, and then join your big long query to that table instead of the users table.

Answer 2

I think that you'd be much better off posting the entirety of what you're trying to solve, because there is likely a cleaner way than what you're trying to do, but the code below should do what you need. I use a Numbers table here. You can create that as a table variable or a permanent table or a temporary table, but the idea remains the same:

SELECT
     U.user_id,
     U.name,
     U.multiplier
FROM
     Users AS U
INNER JOIN Numbers AS N ON
     N.number BETWEEN 1 AND U.multiplier