Database

User
1 Smith
2 Jones

Role
1 DMV
2 CPT

UserRole
1 1               <-- Indicates Smith is in role DMV
1 2               <-- Indicates Smith is in role CPT

JTRizos wrote:

the respective column to a "Y"

Select  UserID
       ,Max(Case When RoleID = 'SecRole1' Then 'Y' Else 'N' End) as SecRole1
       ,Max(Case When RoleID = 'SecRole2' Then 'Y' Else 'N' End) as SecRole2
       ,Max(Case When RoleID = 'SecRole3' Then 'Y' Else 'N' End) as SecRole3
       ,Max(Case When RoleID = 'SecRole4' Then 'Y' Else 'N' End) as SecRole4
       ,...
From    UserRoles
Group By UserID

update table1 set
 AuditView = case when table2.SecurityRole='Audit View' and AuditView='No' then 'Yes' else AuditView end,
 BasicArchive = case when table2.SecurityRole='Basic Archive' then 'Yes' else BasicArchive end,
 BasicModify = case when table2.SecurityRole='Basic Modify' then 'Yes' else BasicModify end,
 BasicView = case when table2.SecurityRole='Basic View' then 'Yes' else BasicView end,
 CentralScanOperations = case when table2.SecurityRole='Central Scan Operations' then 'Yes' else CentralScanOperations end,
 CreateorMaintainParticipant = case when table2.SecurityRole='Create or Maintain Participant' then 'Yes' else CreateorMaintainParticipant end,
 CreateRefundParticipant = case when table2.SecurityRole='Create Refund Participant' then 'Yes' else CreateRefundParticipant end,

from EmergencyContact.dbo.CSEEmployeeRoles as table2 right join EmergencyContact.dbo.CSERolesRolledUp as table1
on (table2.EmployeeID = table1.EmployeeID)

JTRizos wrote:

select @command= 'update EmergencyContact.dbo.CSERolesRolledUp set '+@colname+' = "Yes"where EmployeeID = '+@Eid

Declare @colname varchar(200), @Eid varchar(50),@message varchar(80),@command varchar(200)
Declare my_cursor CURSOR

For Select replace(replace(replace(SecurityRole,' ',''),'/',''),'-','') as SecRole,EmployeeID
from EmergencyContact.dbo.CSEEmployeeRoles
order by EmployeeID, SecRole

open my_cursor

fetch next from my_cursor into @colname,@Eid 
while @@fetch_status = 0
begin
select @message = @colname+' '+@Eid
--print @message

select @command= 'update EmergencyContact.dbo.CSERolesRolledUp set '+@colname+' = ''Yes''
where EmployeeID = '+@Eid
exec (@command)
print @UpdDate
--print @colname
--print @command
fetch next from my_cursor into @colname,@Eid
--print @colname
end
close my_cursor
deallocate my_cursor

Jörgen Andersson wrote:

Or as Tom Kyte[^] has said: "full scans are NOT ALWAYS evil, indexes are NOT ALWAYS good"