CODEWITHSUNDEEP
sql-serversql-server-2008-r2
MAK
MAK

Reputation: 7260

Generate dynamic string

I have the following variable to generate dynamic string.

Example:

If the variable contains two values:

DECLARE @Dynamic_Variables varchar(max) = 'One,Two'
DECLARE @SQL varchar(max)

SET @SQL = '( CASE WHEN [One] > 0 THEN 1 ELSE 0 END ) + ( CASE WHEN [Two] > 0 THEN 1 ELSE 0 END )   AS [Total]'

PRINT(@SQL)

The string should be like this:

( CASE WHEN [One] > 0 THEN 1 ELSE 0 END ) + ( CASE WHEN [Two] > 0 THEN 1 ELSE 0 END )   AS [Total]

If the string contains three values:

DECLARE @Dynamic_Variables varchar(max) = 'One,Two,Three'
DECLARE @SQL varchar(max)

SET @SQL = '( CASE WHEN [One] > 0 THEN 1 ELSE 0 END ) + ( CASE WHEN [Two] > 0 THEN 1 ELSE 0 END ) + ( CASE WHEN [Three] > 0 THEN 1 ELSE 0 END ) AS [Total]'

PRINT(@SQL)

The string should be like this:

( CASE WHEN [One] > 0 THEN 1 ELSE 0 END ) + ( CASE WHEN [Two] > 0 THEN 1 ELSE 0 END ) + ( CASE WHEN [Three] > 0 THEN 1 ELSE 0 END )  AS [Total]

Upvotes: 0

Views: 310

Answers (2)

Veera
Veera

Reputation: 3492

Try the below query: (Change the @string value based on your requirement)

Take a look at the demo, If needs clarification.

DECLARE @string VARCHAR(MAX),
@Split CHAR(1),
@X xml

SELECT @string = 'One,Two,Three',
@Split = ','
SELECT @X = CONVERT(xml,'<root><s>' 
            + REPLACE(@string,@Split,'</s><s>') + '</s></root>')
SELECT STUFF((SELECT ' + ' + Result1 AS FinalResult FROM 
     (SELECT '( CASE WHEN ['+ Result +'] > 0 THEN 1 ELSE 0 END )' 
     AS Result1 
 FROM  
    (SELECT T.c.value('.','varchar(max)') AS Result
     FROM @X.nodes('/root/s') T(c)) AS A ) 
  AS B FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 3, '') + ' 
  AS [Total]'
  AS ExpectedResult

Live Demo here

Upvotes: 1

Saravana Kumar
Saravana Kumar

Reputation: 3729

Use this.

DECLARE @Dynamic_Variables varchar(max)
DECLARE @SQL varchar(max)

 SET @Dynamic_Variables = 'One,Two, Three'

 SELECT STUFF((SELECT  '+' + Value FROM 
 (
   SELECT '( CASE WHEN [' + A.Value + '] > 0 THEN 1 ELSE 0 END )' AS Value 
   FROM 
    (
        SELECT 
            Split.a.value('.', 'VARCHAR(100)') AS Value  
            FROM  
            (
            SELECT CAST ('<M>' + REPLACE(@Dynamic_Variables, ',',
                '</M><M>') + '</M>' AS XML) AS Value 
            ) AS A 
            CROSS APPLY Value.nodes ('/M') AS Split(a)
     ) AS A
  ) AS B
  FOR XML PATH (''), type).value('.', 'Varchar(max)'),1,1,'') + ' AS [Total]'

Upvotes: 1

Related Questions