Conditional INNER Joins

Recently on a newsgroup I found a question about conditional joins in TSQL. The OP was trying to get rid of dynamic SQL and was looking for a way to change the following example script...