locked
Using field data from one table to reference column in another table RRS feed

  • Question

  • I have a table with one record per certificate a user has.   So, user SMITH can have multiple records, one for DMV another for CPT and another for ICD.  SMITH has three certificates but can have up to 20.

    The desire is to have a second table with one record per user with columns for each certificate indicating with Y or N whether the user has that certificate.  The column names are the same as the certificate name (eg. DMV, CPT, ICD,...)

    So, is there a way to read in the first table, use the data in the certificate field (DMV, CPT, ICD, etc) to the reference the column in the second table and update the respective column to a "Y" to indicate the user has that certificate?   For example: if table2:ColumnName(DMV)=table1:Certificate("DMV") then update table2:Column(DMV)="Y"

    The input file we use to create table 1 is in the one record per certificate per user design.  We do not control that.

    The intent is to avoid long Case statements.   If this works, we have another similar application with a possible 130 roles a user can have.   I've Google this multiple was and read lots of possibilities but none seem to indicate this can be done or I just don't understand it.

    Any help will be much appreciated.

     

     

    Friday, December 10, 2010 10:55 PM

Answers

  • I got it to work and thought the code might help others. The --print lines were used for testing so I left them in here.
     
    
    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'',Updated = getdate()
    where EmployeeID = '+@Eid
    exec (@command)
    --print @colname
    --print @command
    fetch next from my_cursor into @colname,@Eid
    --print @colname
    end
    close my_cursor
    deallocate my_cursor
    
    
    
    • Marked as answer by pdljmpr Wednesday, December 29, 2010 7:51 PM
    Wednesday, December 29, 2010 7:48 PM

All replies

  • Based on all the research I've done I created the following.   It seems to work until the set '+@colname+' = "Yes" which makes @colname equal to "Yes" which is not my intent.  My intent is to make the column referenced by @colname set to "Yes".   What am I doing wrong?    Thanx

    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
    where EmployeeID='38'
    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)
    fetch next from my_cursor into @colname,@Eid
    end
    close my_cursor
    deallocate my_cursor
    
    
    Friday, December 17, 2010 11:31 PM
  • I got it to work and thought the code might help others. The --print lines were used for testing so I left them in here.
     
    
    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'',Updated = getdate()
    where EmployeeID = '+@Eid
    exec (@command)
    --print @colname
    --print @command
    fetch next from my_cursor into @colname,@Eid
    --print @colname
    end
    close my_cursor
    deallocate my_cursor
    
    
    
    • Marked as answer by pdljmpr Wednesday, December 29, 2010 7:51 PM
    Wednesday, December 29, 2010 7:48 PM