MS SQL 資料表迴圈範例

print '資料表迴圈處理'

--產生暫存資料表 #score
if exists(select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.#score') )
drop table #score

create table #score (
sno varchar(10), --學號
class varchar(10), --科目
num int --分數
)
insert into #score
select '001', '國', 80
union select '001', '英', 90
union select '001', '數', 55
union select '002', '國', 50
union select '002', '英', 60
union select '002', '數', 75
union select '003', '英', 98
union select '003', '數', 59
union select '004', '國', 88
union select '004', '英', 98
union select '005', '國', 58
union select '005', '數', 69
union select '006', '國', 78
union select '006', '英', 84
union select '006', '數', 59

--select * from #score

print '資料表迴圈處理顯示'
DECLARE score_cursor CURSOR
FOR
SELECT distinct sno, class, num
FROM #score
order by sno, class

OPEN score_cursor
DECLARE @sno varchar(10) -- 學號變數
DECLARE @class varchar(10) -- 科目
DECLARE @num int -- 分數

FETCH NEXT FROM score_cursor INTO @sno, @class, @num
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
PRINT @sno+','+@class+','+convert(varchar(10),@num)
END
FETCH NEXT FROM score_cursor INTO @sno, @class, @num
END
CLOSE score_cursor
DEALLOCATE score_cursor