T-SQL – Flagging Common Values in One Column Based on ID and updating the database

Problem Statement: There are Two Columns, VariableName and ID. I want to map variables in the same column with a two-character alpha code such as ‘aa’, ‘ab’, ‘ac’, ‘ad’…. up to ‘zz’ in T-SQL.

To Illustrate,

Input Data:

VariableName      ID      MapFlag        isMapped
var_1             1         NULL           FALSE
var_2             1         NULL           FALSE
var_3             1         NULL           FALSE
var_4             1         NULL           FALSE
var_5             1         NULL           FALSE
var_1             2         NULL           FALSE
var_2             2         NULL           FALSE
var_3             2         NULL           FALSE
var_10            2         NULL           FALSE
var_11            2         NULL           FALSE
var_1             3         NULL           FALSE
var_10            3         NULL           FALSE
var_3             3         NULL           FALSE
var_7             3         NULL           FALSE
var_1             4         NULL           FALSE
var_2             4         NULL           FALSE
var_4             4         NULL           FALSE

Desired Output

VariableName      ID      MapFlag        isMapped
var_1             1          aa           TRUE
var_2             1          ab           TRUE
var_3             1          ac           TRUE
var_4             1          ad           TRUE
var_5             1          NULL         FALSE
var_1             2          aa           TRUE
var_2             2          ab           TRUE
var_3             2          ac           TRUE
var_10            2          ae           TRUE
var_11            2          NULL         FALSE
var_1             3          aa           TRUE
var_10            3          ae           TRUE 
var_3             3          ac           TRUE
var_7             3          NULL         FALSE
var_1             4          aa           TRUE
var_2             4          ab           TRUE 
var_4             4          ad           TRUE

As you can see above,

• “var_1” , is present in ids 1, 2, 3, 4 and is tagged as ‘aa’
• “var_2” , is present in ids 1, 2 and 4 and is tagged as ‘ab’
• “var_3” , is present in ids 1, 2 and 3 and is tagged as ‘ac’
• “var_4” , is present in ids 1, and 4 and is tagged as ‘ad’
• “var_5” , is present in id 1 BUT IS NOT PRESENT IN THE REST OF THE IDs, so hence NULL
• “var_7” , is present in id 3 BUT IS NOT PRESENT IN THE REST OF THE IDs, so hence NULL
• “var_10” , is present in ids 2 and 3 and is tagged as ‘ae’
• “var_11” , is present in id 2 BUT IS NOT PRESENT IN THE REST OF THE IDs, so hence NULL

To summarize: Essentially, I want to create a mapping flag with the pattern above ‘aa’, ‘ab’, ‘ac’ among different IDs. If the mapFlag is NOT NULL, then isMapped TRUE else FALSE

Lastly, I want to write an “Update” stored procedure to convert the input “MapFlag” column with the Input Data and then update the table with the Output data

Answer

This is the query to get var marks.

select VariableName,  char(ASCII('a')+rn/(ASCII('z') - ASCII('a') +1)) + char(ASCII('a') -1 + rn%(ASCII('z') - ASCII('a') +1)) y
from (
    select  VariableName, row_number() over (order by VariableName) rn
    from mytable
    group by VariableName
    having count(distinct id) > 1
) t