I have the following fields in a record. I have a script that will find the max number to the right of the colon. But also want the corresponding zcode to go with it. Below, column Z4 has the max value of 1. So, I want to update the final Zcode field with "z184".
So, here is the script that I am using to find the column that has the highest score to the right of colon.
SELECT MAX(SUBSTRING(maxzcode,CHARINDEX(':',maxzcode)+1,LEN(maxzcode))) MaxZcode, ID FROM table1 UNPIVOT (MaxZcode FOR E IN (Z1, Z2, Z3, Z4, Z5) )AS unpvt GROUP BY ID;