>
> I was wondering if this is possible:
>
> To make things simpler, I have a table:
>
> CREATE TABLE example
> (text VARCHAR(30),
> number INTEGER);
>
> For example I have this values in it:
>
> someText 4
> someText1 6
> someText2 8
>
> Now I would like to get a ResultSet using only SQL in which I would
> like to have someText in four rows, someText2 in 6 rows and someText2 in
> 8 rows. Basically to get field text repeated for number of times.

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97D27DD5A3580Yazorman@127.0.0.1...
> CREATE TABLE example
> (text VARCHAR(30),
> number INTEGER);
>
> insert example(text, number)
> select 'someText', 4
> union
> select 'someText1', 6
> union
> select 'someText2', 8
> go
> SELECT e.text
> FROM example e
> JOIN numbers n ON n.n BETWEEN 1 AND e.number
> Order by e.text
> go
> drop table example
>
> numbers is a table that holds numbers frmo 1 and up. Here is a way to
> set up:
>
>
>
> -- Make sure it's empty.
> TRUNCATE TABLE numbers
> go
> -- Get a temptable with numbers. This is a cheap, but not 100% reliable.
> -- Whence the query hint and all the checks.
> CREATE TABLE #numbers(n int IDENTITY PRIMARY KEY,
> dummy int NULL)
>
> INSERT #numbers (dummy)
> SELECT TOP 1000000 o1.id
> FROM sysobjects o1
> CROSS JOIN sysobjects o2
> CROSS JOIN sysobjects o3
> CROSS JOIN sysobjects o4
> OPTION (MAXDOP 1)
> go
> -- Verify that table does not have gaps.
> IF (SELECT COUNT(*) FROM #numbers) = 1000000 AND
> (SELECT MIN(n) FROM #numbers) = 1 AND
> (SELECT MAX(n) FROM #numbers) = 1000000
> BEGIN
> DECLARE @msg varchar(255)
>
> -- Insert into the real table
> INSERT numbers (n)
> SELECT n FROM #numbers
>
> SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) +
> ' rows into numbers'
> PRINT @msg
> END
> ELSE
> RAISERROR('#numbers is not contiguos from 1 to one million!', 16, -1)
> go
> DROP TABLE #numbers
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

> A real SQL goody - i didn't think that this is possible with one
> statement.
> My respect, Erland!