Cycling through a table - 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

Transact-SQL (2000)

Cycling through a table

Author

Topic

harrisw48
Starting Member

34 Posts

Posted - 2006-11-22 : 14:46:27

I have a table of data and a table with 2 columns text and a number.

Manchester 10
Leeds 15
London 26

I want to cycle down the criteria table and use the text as the crteria and the number as the number of records.

How can I do it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 14:51:48

You want to convert the data above to a resultset with 51 records?
10 of them is "Manchester", 15 of them are "Leeds" and 26 of them are "London"?

-- prepare test data

declare	@t table (City varchar(100), items int)

insert	@t
select	'Manchester', 10 union all
select	'Leeds', 15 union all
select	'London', 26

select * from @t

-- do the work
select		t.city
from		@t t
inner join	(
			select	number
			from	master..spt_values
			where	name is null
		) q on q.number < t.items

Peter Larsson
Helsingborg, Sweden

harrisw48
Starting Member

34 Posts

Posted - 2006-11-22 : 16:33:54

Ideally I wont to create separate table with the correct number of records for the given criteria.

The criteria table could change regularly but I want the code to remain the same, if that makes sense.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-22 : 20:19:03

quote:
Originally posted by harrisw48

I have a table of data and a table with 2 columns text and a number.

Manchester 10
Leeds 15
London 26

I want to cycle down the criteria table and use the text as the crteria and the number as the number of records.

How can I do it?

Why do you need this?
Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

harrisw48
Starting Member

34 Posts

Posted - 2006-11-23 : 03:20:35

On a regular basis Im asked for numbers of records to be sent to various places based on a big table of data.

Instead of running these as separate queries I want to be able to have a reference table where I store a town and a number of records required.

I then want to be able to run some code that cycles through the reference table, pulling data from the table of data making new tables based on the reference table.

Hope that makes sense.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 03:23:23

You have to give more information.
What is the information in the table used for? To build a dynamic SQL statement to put in a WHERE clause?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources