By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

SQL syntax question

P: n/a
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.
May 29 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Zvonko (zv************@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, es****@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
May 29 '06 #2

P: n/a
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" <es****@sommarskog.se> wrote in message
news:Xn**********************@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, es****@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

May 29 '06 #3

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

bye, Helmut

May 29 '06 #4

P: n/a
Helmut Woess (us****@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, es****@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
May 29 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.