With no success, I am trying to build a dynamic search text on a table with two columns (
Number, both columns are unique).
Number column should be grouped and start a new section of search (with OR). The
Id column should be separated with "," (with IN).
Example (Please ignore the namings, I simplified my case and changed the names):
CREATE TABLE #MyTable ( Id INT, Number INT ); INSERT #MyTable VALUES (6, 3), (7, 3), (8, 3), (16,15), (20, 15);
Expected result: @Query VARCHAR that will contain this:
' SELECT Id, Number FROM #MyTable WHERE ( Number = 3 AND Id IN (6, 7, 8) ) OR ( Number = 15 AND Id IN (16, 20) ) '
Note: The reason I am doing it dynamically because I do a dynamic filters search.
I guess the solution should use
FOR XML PATH('') and
STUFF() methods and probably also a
GROUP BY, but I am not managing to do it (I succeeded to build a dynamic
WHERE on one column but facing lots of difficulties in doing it with 2 columns. Actually, I can build it with a loop but I guess it could be done much better and therefore I ask you for a help.
Thanks in advance.