loop through table records

[Erics44]

Hi
Can I loop through the records in a table in Query Analyser?
Thanks

 

[hmckillop]

Give more details of what it is you want to do.

You can use a WHILE loop, you can use a cursor (though avoid if possible due to performance/locking issues).
But generally if you need to perform multiple operations on one table, you can do it in single query.

So the more detail of what it is you want to do the better.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[Erics44]

I have a table and one field is full of market names. I want to create a view where the market names are the column headers using

sum(case [Market] when 'Market 1' then [Value] else 0 end)as 'Market 1'

to create and populate the market fields in the view

the thing is the market list might change. I could do this in VB .net but would rather keep it in SQL server

any ideas?

 

[hmckillop]

So what other data do you want returned under these new column names?

Essentially you are asking for a column list of data currently stored in a row grouped by market name.

You can do this in a crosstab query, which is somewhat complicated. There are examples of how to do this in the FAQs.

However I would question why you would do this when you can return the data as rows and process it as columns in the UI?




"I'm living so far beyond my income that we may almost be said to be living apart

 

[Erics44]

Id rather arange the data in SQL server because the report it is used for is long standing and i dont want to change anything in the access database if at all posible

Is there a way of doing this

while table1.eof = false

loop

or something like that?

 

[hmckillop]

I am still not 100% clear what you wish to output and what format it is stored in , a simple example of the dataset and expected output would help.

But in answer to the loop

you can do

DECLARE @a int
SET @a = 0
WHILE @a < 100
BEGIN
 --do other stuff here
 SET @a = @a + 1
END
--this will loop 100 times.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Erics44]

I need to loop through the fields in a table

so I have the following table as and example - tblMarkets

MArket
------

Market 1
Market 2
Market 3
Market 4

so I want to loop through these fields by doing something like this

declare @Market as char(8)
while tblMarkets.EOF = false
begin
set @Market = tblMarkets.Market
end

this will loop 4 times the first @Market should = 'Market 1'the second 'Market 2' etc

 

[bborissov]

DECLARE Markets_cursor CURSOR
FOR
SELECT * FROM tblMarkets
declare @Market as char(8)

OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN
      set @Market = tblMarkets.Market
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE Markets_cursor
DEALLOCATE Markets_cursor

This example is taken from BOL

I can't remeber other way to do this w/o using a cursor.

Borislav Borissov

 

[bborissov]

UPDATE:

Remove INTO @tablename from FETCH NEXT statements

Borislav Borissov

 

[Erics44]

brilliant mate
thanks