SELECT Multiplicate rows : SELECT X times same record where X is a value in some column

SELECT Multiplicate rows : SELECT X times same record where X is a value in some column

Post by Kristof Cl » Fri, 20 Apr 2007 08:20:48


Hey,

To explain I will give an example:

Let's say I have a table like this:

| PK | XTimes |
| 1 | 3 |
| 2 | 1 |
| 3 | 2 |

No from this data I want to write some kind of select query so it will
return me:

PK
1
1
1
2
3
3

So I want to get as much rows with the same data as the XTimes column
value is:
Is this possible...

thx in advance
Kristof
 
 
 

SELECT Multiplicate rows : SELECT X times same record where X is a value in some column

Post by xyb » Fri, 20 Apr 2007 11:55:27


use function can do this but it is a clumy achieve.

 
 
 

SELECT Multiplicate rows : SELECT X times same record where X is a value in some column

Post by Kristof Cl » Fri, 20 Apr 2007 17:20:42


> On 49 70 Kristof Clev<rs wrot>: >> >> >>> > > He>, >>> > > To explain I will give an exampl>: >>> > > Let's say I have a table like thi>: >>> > > | PK | XTimes>|> > > | 1 | 3 gt;|> > > | 2 | 1 gt;|> > > | 3 | 2 gt;| >>> > > No from this data I want to write some kind of select query so it wi>l> > > return m>: >>> > > >K> > >>1> > >>1> > >>1> > >>2> > >>3> > >>3 >>> > > So I want to get as much rows with the same data as the XTimes colu>n> > > value i>:> > > Is this possible.>. >>> > > thx in advan>e> > > Krist>f >>
> use function can do this but it is a clumy achieve.- Tekst uit oorspronkelijk bericht niet weergeven>- >>
> - Tekst uit oorspronkelijk bericht weergeven -

How would I go about achieving this then?

(Maybe it looks clummy, but I am in the middle of creating a system
that convert foreign data to our system, In the concurent data table
the data is stored in the first way (with XTimes column) but we store
it XTime the row.
 
 
 

SELECT Multiplicate rows : SELECT X times same record where X is a value in some column

Post by Plamen Rat » Fri, 20 Apr 2007 21:48:40

Here is one way to do this on SQL Server 2005:

CREATE TABLE Foo (PK INT PRIMARY KEY,
XTimes INT);

INSERT INTO Foo VALUES (1, 3);
INSERT INTO Foo VALUES (2, 1);
INSERT INTO Foo VALUES (3, 2);

WITH Exploded (PK, XTimes, Num) AS
(
SELECT PK, XTimes, XTimes
FROM Foo
WHERE XTimes > 0
UNION ALL
SELECT PK, XTimes, Num - 1
FROM Exploded
WHERE Num > 1
)
SELECT PK FROM Exploded
ORDER BY PK;

HTH,

Plamen Ratchev
http://www.yqcomputer.com/