473,320 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL syntax question

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
4 2399
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
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
A real SQL goody - i didn't think that this is possible with one statement.
My respect, Erland!

bye, Helmut

May 29 '06 #4
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

699
by: mike420 | last post by:
I think everyone who used Python will agree that its syntax is the best thing going for it. It is very readable and easy for everyone to learn. But, Python does not a have very good macro...
4
by: Aaron Walker | last post by:
Greetings, I'm attempting to write my first *real* template function that also deals with a map of strings to member function pointers that is making the syntax a little tricky to get right. ...
2
by: bor_kev | last post by:
Hi, First of all, i want to use the new managed class syntax and STL.NET under Microsoft Visual (C++) Studio 2005 Beta. I read in a Microsoft...
5
by: r.nikhilk | last post by:
Hi, Currently, we are porting C++ applications from 32 bit to 64 bit on AIX platform. (The current version of AIX is 5.3 and xlC verison is 8.0). We are able to compile the applications by...
3
by: astromog | last post by:
I have some significantly extended syntax for Python that I need to create a reference implementation for. My new syntax includes new keywords, statements and objects that are sort of like classes...
11
by: deppy_3 | last post by:
Hi! The syntax of fputs() is similar with the syntax of fgets(); For example if we have:fgets(str,maxlen,stdin) which is the syntax of the fputs();
8
by: Smithers | last post by:
Are there any important differences between the following two ways to convert to a type?... where 'important differences' means something more profound than a simple syntax preference of the...
2
by: berrylthird | last post by:
This question was inspired by scripting languages such as JavaScript. In JavaScript, I can access members of a class using array syntax as in the following example: var myInstance:myClass = new...
17
by: trose178 | last post by:
Good day all, I am working on a multi-select list box for a standard question checklist database and I am running into a syntax error in the code that I cannot seem to correct. I will also note...
6
by: Daniel | last post by:
I hope this question is OK for this list. I've downloaded Rpyc and placed it in my site packages dir. On some machines it works fine, on others not so much. Here is one error I get when I try...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.