Crazy joins and insane SQL

I am trying to write reports at work off of data that is not really conducive to reporting.  The biggest problem is a field that has values separated by commas.  It can hold an agent's name or the queue or queues that have access to work it.  I found this article on stack overflow, http://stackoverflow.com/questions/10019557/join-sql-server-tables-on-a-like-statement, and it is helping me join to a reference table, but I still can't obviously get rows with more than one value in the table to count only once, but I have also been running into a problem of trying to join the agent ID in the column to our agent hierarchy data.  I starting using substring and charindex before I found this method for the queues.  I just realized I need to use this same technique for this join.  I need to test this in the morning to see if I can fix the problem with this technique instead of a nasty case statement with substrings and charindexes.