Looping through a row and retrieving data relative to the value in each column

I have a long running query because it is reading a very large database.  The issue is there is a string of codes that each have an order number.  There can be as many as 30 on a  line. The order is then matched to another table to get the actual value.  What is the best way to loop through this row to get the codes for all of the 20 values.  This is an SQL statement.
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
The issue is there is a string of codes that each have an order number.

How are they delimited in that string? You could use a string split function and actually SQL has its own from 2016 and up - just have a look here https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2016
Essentially is the same thing if you don't have it depending on the delimiters you could write your own or if you google sql seting split function examples I bet you'll find one. Like this for example https://sqlperformance.com/2012/07/t-sql-queries/split-strings
moriniaAdvanced Analytics AnalystAuthor Commented:
It is not a string.  It is a column than can have multiple values.  Currently I have multiple joins of the same table and check the value.  It will be value1, or value2 or value3........value30.  I then use that value as a key to read another table.  Currently I am using the same two table just giving them different alias.
slightwv (䄆 Netminder) Commented:
If you have a single column that you need to look up in two tables, it is a simple join.  I'm guessing that isn't the solution so I'm not understanding your tables.

Please post sample data and expected results.  We don't need all 30 values.  Just 2 or 3 to get the general concept.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
moriniaAdvanced Analytics AnalystAuthor Commented:
Attached is a very simple query with four columns.  I am doing this 30 times.  I am hoping to find a more efficient approach.
Sample.txt
Dung DinhDBA and Business Intelligence DeveloperCommented:
Do you have the index on this colum?
slightwv (䄆 Netminder) Commented:
Can't you join with an IN clause?

select ...
...
ON  FILEA.CLM_KEY=DIAG1.CLM_KEY AND DIAG1.DIAG_CD_ORDR_NBR in ('1','2','3','4')
...

Then you can PIVOT or use CASE statements to get the individual columns.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moriniaAdvanced Analytics AnalystAuthor Commented:
Thanks so much. It wither perfectly.
slightwv (䄆 Netminder) Commented:
Glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSAS

From novice to tech pro — start learning today.