CODEWITHSUNDEEP
sqlsql-servermultiple-columnsconcatenation
Praveen Rawat
Praveen Rawat

Reputation: 658

form a query as per column values

I am using sql server 2012 and I have a table like this: 

FieldName     FieldValue
DivisionId    1
DivisionId    2
DivisionId    3
CompanyId     2
CompanyId     3
LocationId    1

What i want is concatenate columns and form a where clause query like this 

(DivisionId=1 OR DivisionId=2 OR DivisionId=3) AND
(CompanyId=2 OR CompanyId=3) AND
(LocationId = 1)

What I was able to figure out is, I need to concatenate columns values like this

DECLARE @Query VARCHAR(MAX)

SELECT @Query =
ISNULL(@Query,'') + IIF(@Query IS NOT NULL, ' AND ', '') + CONCAT(DF.FieldName,'=',DA.FieldValue)
FROM TABLE

SELECT @Query;

But this code will not handle OR condition.

Upvotes: 0

Views: 196

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Try following solution:

DECLARE @eav TABLE (
    FieldName       NVARCHAR(128) NOT NULL,
    FieldValue      VARCHAR(50) NOT NULL
)
INSERT  @eav (FieldName, FieldValue) 
SELECT 'DivisionId', 1 UNION ALL
SELECT 'DivisionId', 2 UNION ALL
SELECT 'DivisionId', 3 UNION ALL
SELECT 'CompanyId ', 2 UNION ALL
SELECT 'CompanyId ', 3 UNION ALL
SELECT 'LocationId', 1 

DECLARE @Predicate NVARCHAR(MAX) = N''
SELECT @Predicate = @Predicate
        + CASE WHEN rn_asc = 1 THEN ' AND ' + FieldName + ' IN (' + LTRIM(FieldValue) ELSE '' END
        + CASE WHEN rn_asc > 1 THEN ', ' + LTRIM(FieldValue) ELSE '' END
        + CASE WHEN rn_desc = 1 THEN ') ' ELSE '' END
FROM (
    SELECT  *, 
            rn_asc = ROW_NUMBER() OVER(PARTITION BY x.FieldName ORDER BY x.FieldValue),
            rn_desc= ROW_NUMBER() OVER(PARTITION BY x.FieldName ORDER BY x.FieldValue DESC)
    FROM    @eav x
) y
ORDER BY FieldName, FieldValue
SELECT @Predicate = STUFF(@Predicate, 1, 5, '')
SELECT @Predicate
-- Results: CompanyId  IN (2, 3)  AND DivisionId IN (1, 2, 3)  AND LocationId IN (1)

Then you could use @Predicate to create a dynamic SQL SELECT statement (for example)

DECLARE @SqlStatement NVARCHAR(MAX)
SET @SqlStatement = 'SELECT ... FROM dbo.Table1 WHERE ' + @Predicate
EXEC sp_executesql @SqlStatement

Upvotes: 1

Related Questions