Duplicating rows in a table by count
-
I have a table with a number of fields. The last field gives a count. I would like to be able to produce a duplicate of this table, but instead of having a count I would like to duplicate the rows (except the key) for a number of times as detailed by the count. What would be the most efficient way of doing this?Update:
Do people understand this question?
If I have a row like
1 XXXXXXXXXX 2
I want a new table to have the following inserted into it
1 XXXXXXXXXX
2 XXXXXXXXXX
and I want this action to be repeated for all the rows in the table.
-
- select * from table t
- join master.dbo.spt_values n
- on n.number < t.count
- where n.type = 'P'
This will work for counts up to 2047.
You would probably want to make your own numbers table rather than using spt_values.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Why do you have the where clause?:
where n.type = 'p'
By the way, the maximum count I need is:
18711
So I will need to construct my own table for this. Does anyone have suggestions how I can do this?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply