Searching for keys in key-value columns and retrieving values in another table.
Whether you are a database programmer or an applications programmer working with Transact-SQL, there are common problems that reoccur. One such problem is when you need to search for keys in columns set up as key-value type record structures while inserting the value into a field in another table. This blog will provide a scenario and a solution for exactly this situation.
A real-world example of this problem might be in the financial bond industry where one table has fields giving the bond rating value by agencies such as Moody’s, S&P and Fitch, with the second table listing the key-value fields for bonds being processed. In the processing, there are multiple key-value fields where the key field is the code for a rating agency and the value field is the assigned rating value. The code could be any one of the rating agencies or no value all. This can be easily solved using the Transact-SQL CASE.
The image below is a transmission file containing rating agency information. The information is from different sources.
The rating agency information is inserted into a table used for displaying the information. The case statement searches each of the possible code fields for a rating agency. If nothing is found, a space is then entered as the default. This is done for each agency.
This is showing the final result of the SQL statement from above. The values are put into the appropriate field, no matter where they are positioned in the transmission file.
This logic can be used anywhere columns are set up in a key-value record structure and the keys need to be searched in order to get their values.
