Impossible Query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Impossible Query?

Author

Topic

Kyle.Stevens
Starting Member

26 Posts

Posted - 2007-09-24 : 16:37:33

I have a problem with Crystal Reports that I would like to solve at the SQL level. I doubt this is even possible with SQL.

I have a table with product names and quantities in it. I want to use Crystal Reports to print labels for each product. The quantity of labels to be printed would be equal to the quantity for the product. For example, if product A has a quantity of five, then five labels need to be printed for that specific product.

My question is, using SQL how can I select multiple rows based on a column in the table being selected from?

Example Table:


Column1 | Column2
A         5

Given the above row, I would want a query to result in the following rows:


Column1
A
A
A
A
A

Other than making a temporary table which I find dirty, I don't know any other solution to this problem. If anyone could point me in the right direction, I would be very thankful and will post my final solution for all to see.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-24 : 17:01:15

select column1
from yourTable
cross join numbers
where n <= column2

Solution requires that the numbers table be present.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kyle.Stevens
Starting Member

26 Posts

Posted - 2007-09-24 : 18:53:40

Thanks, tkizer. That sounds like it should work. I will try it out from work tomorrow.

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 02:22:29

I'm sure you can make a numbers table

but in case it helps:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&SearchTerms=Create+Tally+Table#169653

Kristen

Kyle.Stevens
Starting Member

26 Posts

Posted - 2007-09-25 : 08:53:04

Thanks again Kristen. And we moved our database to that new server this morning. It is working wonderfully, especially with the new indexes! Thanks for that too.

Kyle.Stevens
Starting Member

26 Posts

Posted - 2007-09-25 : 08:56:45

Thanks tkizer. I tried out your solution this morning, and it works perfectly.

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-25 : 09:12:43

quote:
Originally posted by Kyle.Stevens

Thanks tkizer. I tried out your solution this morning, and it works perfectly.

and....you where expecting something else?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 05:43:27

Brett you need to get out more! That's "Praise". If you try code from a book you first have to fix all the typographical errors ... not here with Pukka SQL Team Code(tm)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources