日历

2008 7.7 Mon
日 一 二 三 四 五 六
  12345
6789101112
13141516171819
20212223242526
2728293031  
«月» 2008 - 7 «年»

日志分类

存　档

文章搜索

日志文章

2008年05月15日 19:28:15

实现行向列转化的sql

使用临时表的方式实现：
ALTER PROCEDURE [enterprise].[CustomUserInfo_GetPermissions]
(
  @CompanyID nvarchar(36),
  @DivisionID nvarchar(36),
  @DepartmentID nvarchar(36),
  @UserID nvarchar(36)
)
AS
BEGIN
  declare @BodyID nvarchar(36)
  declare @BodyType nvarchar(36)
  declare @PermissionID nvarchar(36)
  declare @Accessible bit

  select @BodyID=BodyID,@BodyType=BodyType
  from UserInformation
  where CompanyID=@CompanyID
  and DivisionID=@DivisionID
  and DepartmentID=@DepartmentID
  and UserID=@UserID

create table #temp_permission
(
BodyID nvarchar(36),
BodyType nvarchar(36)
)

insert into #temp_permission(BodyID,BodyType) values(@BodyID,@BodyType)

declare cPermission cursor for
  select per.PermissionID,
    case when exists
    (select 1 from UserGroupDetail AS ugd

    INNER JOIN UserGroupPermission AS up

    ON ugd.CompanyID=up.CompanyID and ugd.DivisionID=up.DivisionID and ugd.DepartmentID=up.DepartmentID and ugd.UserGroupID=up.UserGroupID
    INNER JOIN UserInformation as ui
    ON ugd.CompanyID=ui.CompanyID and ugd.DivisionID=ui.DivisionID and ugd.DepartmentID=ui.DepartmentID and ugd.UserID=ui.UserID
    WHERE ui.UserID=@UserID and ugd.CompanyID=@CompanyID and ugd.DivisionID=@DivisionID and ugd.DepartmentID=@DepartmentID and up.PermissionID =per.PermissionID and up.Accessible =1
    ) then 1 else 0 end
    as Accessible
  FROM PermissionInformation as per
  WHERE per.CompanyID=@CompanyID and per.DivisionID=@DivisionID and per.DepartmentID=@DepartmentID
Open cPermission
fetch next from cPermission into @PermissionID,@Accessible
while @@fetch_status=0
begin
declare @sql1 nvarchar(1000)
declare @sql2 nvarchar(1000)
set @sql1='alter table #temp_permission add '+ @PermissionID +' bit'
exec(@sql1)
set @sql2='update #temp_permission set '+@PermissionID+'='+cast(@Accessible as char(1))
exec(@sql2)
fetch next from cPermission into @PermissionID,@Accessible
end
close cPermission
deallocate cPermission
select * from #temp_permission
 
END







Tags： 行列转换  

类别： database |  评论(1) |  浏览(1173) |  收藏
1楼 [匿名]楼主好，向您请� 2008年05月27日 09:37:50 Says:
我的博客地址：http://365.blog.ccidnet.com，现在我正为这个问题发愁呢，你的方法很好，希望有些注释会更好些，这也是我们开发人员的一个好习惯。我的MSN： ljg365@sohu.com希望与您多多交流。谢谢。
发表评论