SQL Server2000中游标和行专列的使用例子

SQL Server 2008,2005中一些行专列什么的都有了函数,比较简单.使用2000时都得写sql语句实现。

如果你还在使用2000,而且是个新手,对游标和行转列不大了解。可以看看这段代码。

--首先通过寻找可见的且未删除的tab来为后面做准备select a.tabid,a.tabname into #tab from tabs a --------------------88 rowsinner join (select tabid from tabs where level=1 and isvisible=1 and isdeleted=0) as bon a.parentid=b.tabidwhere a.level=2 and a.isvisible=1 and a.isdeleted=0--创建一个临时表用来存放role对应所有菜单以及是否能看到这个菜单的flagcreate table [dbo].[#tmproletab] ([rolename] [varchar] (100) ,[tabname] [varchar] (100) ,[flag] [int])--drop table #tmproletab--首先把所有的role名称和ID都放到一个游标中declare @rolename varchar(100),@roleid varchar(100)declare Role cursor fast_forward read_only for( select roleid,rolename from roles)open Rolefetch next from Role into @roleid,@rolenamewhile @@Fetch_Status = 0begin insert into #tmproletabselect @rolename as 'rolename', tabname, case when tabid in(select tabid from tabpermission where roleid=@roleid) then '1' else '0' end as 'flag' from #tabfetch next from Role into @roleid,@rolenameendclose Roledeallocate Role--select * from #tmproletab--行转列来实现需要的格式declare @sql varchar(8000) set @sql = 'select rolename ' select @sql = @sql + ' , max(case tabname when ''' + tabname + ''' then flag else 0 end) [' + tabname + ']' from (select distinct tabname from #tmproletab) as a set @sql = @sql + ' from #tmproletab group by rolename' exec(@sql)

 

这里用到三个表,tabs, roles和tabpermission。 后面这个表用来关联前面两个表。我主要是想让role表中的每个rolename都先对应到tabs表中的每条记录,使用游标实现,然后再用行转列实现要求的格式。