Hi all!

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.

ResukltSet:
someText
someText
someText
someText
someText1
someText1
someText1
someText1
someText1
someText1
someText2
someText2
someText2
someText2
someText2
someText2
someText2
someText2


Please help.

Zvonko (zvonko_NOSPAM_@velkat.net) writes:
>
> 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.

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks for the very prompt answer. That was just was I was looking for. I
redesigned it so the numbers table holds only numbers from 1 to 1000, and
everything works fine. See guys everything is possible with sql if you know
the gurus like Erland.

Many, many thanks to you.

Bye



"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/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

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

bye, Helmut

Helmut Woess (user22@inode.at) writes:
> A real SQL goody - i didn't think that this is possible with one
> statement.
> My respect, Erland!

At this point I feel obliged to point out that using a table numbers is
not an idea that I have come up with, but something I've picked up by
following the SQL Server newsgroups over the years. I will also have
to admit that as a system developer I am overly fond of using a table
numbers. It's a great idea - until you exceed the highest number in the
table, and your query no longer produces the right result.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx