Assuming that your Rules table always has TWO UnitId values, separated by a comma, then I would suggest using a function to return the two strings, e.g.:
create function TwoStrings (
declare @outString varchar(255)
declare @temp varchar(128)
declare @unitID1 varchar(32)
declare @unitID2 varchar(32)
set @temp = select UnitID from Rules where id = @inID
set @unitID1 = substring(@temp, 1, 2)
set @unitID2 = substring(@temp, 3, 2)
set @outString = (select Description from UnitTypes where UnitID = @unitID1 +
‘, ‘ + select Description from UnitTypes where UnitID = @unitID2)
Then, the result would be obtained from:
select Id, TwoStrings(Id) from Rules
Now – this code snippet violates a lot of my rules about handling unexpected conditions, testing for nulls, etc. It will only work when ALL of the input rows have exactly “xx,yy” in the UnitID column – no white space, no single values, etc.
Proper code would test the inputs and ensure the unit id values were valid, handle unit ids that are longer (or shorter?) than 2 characters, watch for white space, etc. Note that if any records contain only one unit id, that row will return a null string in the above code, as the string concatenation will return null if any of the parts are null.
A much better data model would be to have separate rows for each pairing of UnitID and ID in the Rules table. Embedding values to use for joins or indexing in the middle of a string is not a recommended procedure.