The question first needs to be clarified.
1. How are the tables related? I.e., if the first table has “1,2,3,4″ in column 1, and the second table has “1,2,3″ in column 1, what is the rule used to join the tables?
2. The example format has an error (or two) – either the example pattern should end with “…(y,z)” or the example output should end with “…(3,4)”. Otherwise, the pattern to output relationship makes no sense.

Any attempt to do this strictly in T-SQL without writing a function would be incredibly inefficient. I would think the best approach would be to write a function that took the pattern and source data and returned the result string, e.g.:

set @output = MyFunction( '(1;2),(1;2;3;4),(2,4)' , 'x,y,w,z' )

The function would return ‘(x,y),(x,y,w,z),(y,z)’.

Then, your query would look something like:

select 
   tblpattern.col1,
   MyFunction ( tblpattern.col2,  tbldata.col2 )
from
   FirstTable tbldata
       join SecondTable tblpattern on tblpattern.?what? = tbldata.?what?

The function itself wouldn’t be that difficult.

Yes, the data provided seems to be incorrect.

It would probably be inefficient using functions as well, so IMO the best solution would be redesigning the second table.

The data looks correct, they are joining the values in table2.col2 with table1.col1 and using table1.col2 as the replacement value.

However, I agree with both statements provided, it would be best to redesign the table, but a function can also be written that separates the values in the brackets, does a lookup for the values, and then puts the values back into a bracketed return value.

“The data looks correct, they are joining the values in table2.col2 with table1.col1 and using table1.col2 as the replacement value.”

I don’t think so.

This is the first table:

1	x
2	y
3	w
4	z

So, (2,4) should be (y,z), not (w,z)

But it doesn’t really matter. It doesn’t affect what has been suggested.

So, (2,4) should be (y,z), not (w,z)

Unless the comma and the semi-colons make a difference — but that would make it even more questionably complex.

I can almost see a possibility of a variation of a recursive CTE being used by a UDF to return the results, but I can’t guess why anyone would want this to work in SQL. I’ve seen stranger things though. (I couldn’t guess for those either.)

Tom

As long as the function is only used in the Select portion of the statement (i.e., only applied to the rows already selected by the Where clause), this wouldn’t be that inefficient.
Even if the tables were redesigned to split the values into separate columns, the computational complexity/overhead would be similar.

I agree Kccrosser, but a better table design would simplify this query and probably many others in the future.

select m.category
,(select case c.name
when ‘(1;2)’ then ‘(x,y)’
when ‘(1;2;3;4)’ then ‘(x,y,w,z)’
when ‘(2,4)’ then ‘(w,z)’
end as name
from Prod_Category c where c.id=m.category ) name
from Prod_Master m
inner join Prod_Category c on c.id=m.category

To the last poster (with the unimaginative name):
Suggest you read the full thread before posting. The problem wasn’t to solve the one specific case, but to be data-driven from control tables.
Explicitly converting through a SQL case statement would potentially require enumerating all possible combinations of 1..4 variables in any combination (and order).
For the simple case of 4 variables, that would be 64 case branches. Adding a fifth variable would require 325 case branches.