How to create Dynamic SQL based on two tables

Question

I'm trying to construct a dynamic SQL based on the Cartesian product of two tables

Table1 Colunm1 Dev Test

table2 Column2 ProductNumber ProductDesc

here the result that I want:

(ProductNumber LIKE '%dev%' OR ProductDesc LIKE '%dev%' ) 
    AND (ProductNumber LIKE '%Test%' OR ProductDesc LIKE '%Test%')

I tried to do some simple query like below but I cannot manage to add a AND instaed a OR between each column1 value

SELECT @sql = COALESCE(@sql + Colunm2 + ' LIKE ''%'  + Colunm1 + '%'' OR ','') 
    from Table1, Table2

that give:

ProductNumber LIKE '%dev%' OR 
ProductDesc LIKE '%dev%' OR 
ProductNumber LIKE '%Test%' OR 
ProductDesc LIKE '%Test%'

I can do it with a while but if you have a better solution I can use it

Answer 1

I note that you want your ORs grouped by table1.column1, so the below should work:

SELECT CASE WHEN row_num = 1 THEN ') AND (' ELSE '' END || code as code
FROM (
SELECT Column1, ROW_NUMBER() OVER (GROUP BY Column 1) as row_num, COALESCE( Column2 + ' LIKE ''%' + Column1 + '%'' OR ','') as code
FROM Table1, Table2 
) gen_code
ORDER BY Column1, row_num

(I haven't tested it, but I have written lots of such code before) It adds an additional ') AND (' at the beginning, but you can get it away if you use another ROW_NUMBER over the whole thing. It also lacks a closing ')', but you get the idea. Other than that, use your current approach with a variant of above code. Note that I assumed you have no string aggregation function available.