Concatenating Rows to create WHERE clause.

• DiegoPeña

  Right there with Babe

  Points: 749

  More actions

  November 27, 2012 at 8:49 am

  #285446

  Hi,

  Im creating a dynamic where clause where the operators and conditions are stored in a table:

  create table MyTable

  (

  columnName nvarchar(80),

  Operator nvarchar(20),

  Condition nvarchar(80)

  )

  INSERT INTO MyTable VALUES('columnA','LIKE','Audi')

  INSERT INTO MyTable VALUES('columnA','NOT LIKE','GT')

  INSERT INTO MyTable VALUES('columnB','<=','500')

  I resolved this by creating some nested cursors (yeah I know, cursors... and nested :S ).

  this the result:

  'SELECT * FROM OtherTable WHERE columnA LIKE '%Audi%' AND ColumnA NOT LIKE '%GT%' OR columnB <= 500'

  I don't want to use cursors so that is why Im posting this here.

  Can anyone give me a hand on this please, I want to learn how to resolve this without using cursors.

  Thank you.

  PD: Sorry for my english, Im still learning.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  November 27, 2012 at 9:11 am

  #1562743

  something like this?

  i need an anchor for the outer query, so i used the columnName;

  your real table might have a queryId or something, i would guess, if you are testing multipel columns in in the WHERE statement.

  youll also have to un-escape the gt/lt stuff that got changed via xml.

  /*

  SELECT * FROM OtherTable WHERE 1 = 1 AND <= 500

  SELECT * FROM OtherTable WHERE 1 = 1 AND LIKE %Audi% AND NOT LIKE %GT%

  */

  create table MyTable

  (

  columnName nvarchar(80),

  Operator nvarchar(20),

  Condition nvarchar(80)

  )

  INSERT INTO MyTable VALUES('columnA','LIKE','Audi')

  INSERT INTO MyTable VALUES('columnA','NOT LIKE','GT')

  INSERT INTO MyTable VALUES('columnB','<=','500')

  --'SELECT * FROM OtherTable WHERE columnA LIKE '%Audi%' AND ColumnA NOT LIKE '%GT%' OR columnB <= 500'

  SELECT DISTINCT

  ' SELECT * FROM OtherTable WHERE 1 = 1 '

  + ( SELECT ' AND ' + columnName + ' '

  + CASE

  WHEN Operator LIKE '%LIKE%'

  THEN Operator + ' ' + '%' + Condition + '%' + ' '

  ELSE Operator + ' ' + Condition + ' '

  END

  FROM MyTable T2

  WHERE T1.columnName = T2.columnName

  FOR XML PATH(''))

  FROM MyTable T1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• DiegoPeña

  Right there with Babe

  Points: 749

  More actions

  November 27, 2012 at 9:25 am

  #1562747

  Thank you that was really fast.

  I changed it to un-scape the gt/lt character.

  --'SELECT * FROM OtherTable WHERE columnA LIKE '%Audi%' AND ColumnA NOT LIKE '%GT%' OR columnB <= 500'

  SELECT DISTINCT

  ' SELECT * FROM OtherTable WHERE 1 = 1 '

  + ( SELECT ' AND ' + columnName + ' '

  + CASE

  WHEN Operator LIKE '%LIKE%'

  THEN Operator + ' ' + '%' + Condition + '%' + ' '

  ELSE Operator + ' ' + Condition + ' '

  END

  FROM MyTable T2

  WHERE T1.columnName = T2.columnName

  FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')

  FROM MyTable T1