Generate dynamic string

Question

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]

Veera · Accepted Answer · 2014-12-10 07:24:55Z

1

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

edited Dec 10, 2014 at 7:24

answered Dec 10, 2014 at 7:19

Veera

3,4922 gold badges16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Saravana Kumar · Accepted Answer · 2014-12-10 07:24:43Z

1

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]'

edited Dec 10, 2014 at 7:24

answered Dec 10, 2014 at 7:16

Saravana Kumar

3,7295 gold badges19 silver badges39 bronze badges

Collectives™ on Stack Overflow

Generate dynamic string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related