CODEWITHSUNDEEP
sqlt-sql
Ctrl-Zed
Ctrl-Zed

Reputation: 687

Building a SQL 'where' clause from input (table) data

Intro

I'm creating a SQL Server stored procedure to dynamically get data from large table(s). There are many (30+) columns which may / may not need to be returned with selected data.

I have a dynamic SQL select query, using an unpivot (so columns are listed as rows), working as expected... Now, I need to filter these results.

Input table:

Column
A
B

Table:

A          B           C
---------------------------
value1     valueZ 
value2     valueY
value3     valueX
value0                 valueA
value4
           valueX

Expected results:

Col  Value    Count
-------------------
A    value1   1 
A    value2   1 
A    value3   1
A    value0   1
A    value4   1
B    valueZ   1
B    valueY   1
B    valueX   2

(no C due input column selection)

Question

As an input, I have a table type listing the columns I need to return. I intend to extend this to include values to filter the given columns.

Example table type input:

Column    Value
-----------------
A         value1
A         value2
A         value3
B         valueX
B         valueY

From this, I need to build a where clause like this. Same columns are ORed; when column changes, it is ANDed.

WHERE (A = 'value1' 
       OR A = 'value2' 
       OR A = 'value3') 
  AND (B = 'valueX' OR B = 'valueY')

So far, I've got a COALESCE, but don't know how to determine the column change - I really don't want a cursor.

As a C# dev - can SQL offer me any 'magic' loops to achieve this?

I can also use C# to parse the clause as a string, but kind of need to keep that bit easy!

Many thanks.

Upvotes: 0

Views: 1857

Answers (3)

Ctrl-Zed
Ctrl-Zed

Reputation: 687

With most thanks to @user2114537, I came up with this (it's not pretty!). I do need to apply the dynamic filter BEFORE the pivot.

We are using SQL Server 2008, so LEAD() is unavailable, I also used COALESCE instead of @user2114537's WHILE (please review their answer, https://stackoverflow.com/a/54592090/4616330, for SQLServer >= 2012).

-- create a tmp table var for and/or where data
DECLARE @t TABLE([Column] nvarchar(50), [Value] nvarchar(50), [andOr] nvarchar(7))

-- create a table with row numbers (for joining to self)
;WITH cte AS
 (SELECT f.[Column], f.[Value], ROW_NUMBER() OVER (ORDER BY f.[Column], f.[Value]) AS [RowNo]
    FROM @InputTable f
)
-- join table to itself to reproduce 2012's LEAD function and insert AND/OR based on col being same as previous
INSERT INTO @t([Column], [Value], [andOr])
SELECT c1.[Column], c1.[Value],  CASE WHEN c1.[Column] = c2.[Column] THEN ') AND (' ELSE ' OR ' END AS [andOr]
FROM cte c1 LEFT OUTER JOIN cte c2 ON c1.[RowNo] = c2.[RowNo] + 1

-- build a where clause from andOr info
DECLARE @where AS NVARCHAR(MAX),
    @whereBuilt AS NVARCHAR(MAX)
SELECT @where = COALESCE(@where, '') + (QUOTENAME([Column]) + '=''' + [Value] + ''' ' + [andOr] + ' ')
FROM @t

-- fiddle to get a valid where...
SET @whereBuilt = '(' + SUBSTRING(@where, 1, LEN(@where) - 4) + ')'

PRINT @whereBuilt

Thank you for all comments and answers.

Upvotes: 0

MladenB
MladenB

Reputation: 161

You can try this. First create temp table with script below. COLUNM_A is your column and VALUE_A is your value column.

 SELECT ROW_NUMBER() OVER (ORDER BY A.COLUMN_A) AS ID, A.COLUMN_A, A.VALUE_A, 
 CASE WHEN LEAD(A.COLUMN_A,1) OVER (ORDER BY A.COLUMN_A) = A.COLUMN_A THEN 1 ELSE 0 END AS IDEN 
 INTO #T1 
 FROM dbo.YOUR_TABLE A

temp table look like this, and you have a new column IDEN for define 'OR' and 'AND' statment

SELECT * FROM #T1

after that you try this:

DECLARE @SQL_WHERE NVARCHAR(1500),
        @val1 INT,
        @valmax INT

SET @valmax = (SELECT COUNT(*) FROM #T1)
SET @val1 = 1
SET @SQL_WHERE = '('
WHILE @val1 <= @valmax
BEGIN
 SET @SQL_WHERE = @SQL_WHERE + (SELECT COLUMN_A + ' = ''' + VALUE_A + CASE WHEN IDEN = 1 THEN ''' OR ' ELSE ''') AND (' END
    FROM #T1 WHERE ID = @val1)
 SET @val1 = @val1 + 1
END 
SELECT 'WHERE ' + SUBSTRING(@SQL_WHERE, 1 , LEN(@SQL_WHERE) - 5)

Last select will return correct WHERE clause

Upvotes: 1

iamdave
iamdave

Reputation: 12243

If I understand your situation correctly, you already have the unpivoted output as you require, you just need to filter it from a given set of values?

If this is the case, you can put those values into a derived table and filter the first dataset through an inner join:

declare @t table(Col varchar(1),Val varchar(6),Cnt int);
insert into @t values('A','value1',1),('A','value2',1),('A','value3',1),('A','value0',1),('A','value4',1),('B','valueZ',1),('B','valueY',1),('B','valueX',2);

declare @f table(Col varchar(1),Val varchar(6));
insert into @f values('A','value1'),('A','value2'),('A','value3'),('B','valueX'),('B','valueY');

select t.*
from @t as t
    join @f as f
        on t.Col = f.Col
            and t.Val = f.Val;

Output

+-----+--------+-----+
| Col |  Val   | Cnt |
+-----+--------+-----+
| A   | value1 |   1 |
| A   | value2 |   1 |
| A   | value3 |   1 |
| B   | valueY |   1 |
| B   | valueX |   2 |
+-----+--------+-----+

Upvotes: 1

Related Questions