生成动态字符串

Question

我有以下变量来生成动态字符串。

示例

如果变量包含两个值：

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)

字符串应该如下所示：

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

如果字符串包含三个值：

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)

字符串应该如下所示：

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

Answer 1

尝试以下查询：(根据您的需求更改@string值)

如果需要澄清，请看演示。

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

现场演示

Answer 2

使用这个。

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