SQL IF在SELECT语句中设置参数值

EDIT: This problem has been solved using a better method. Rather than using a variable I am concatenating results to get my desired output.

编辑:此问题已使用更好的方法解决。而不是使用变量我连接结果以获得我想要的输出。

I am trying to get values from several fields from a couple of different tables and that works fine. But I would also like to have a "@Description" which is a concatenation of hard coded text based on existing values for each row.

我试图从几个不同的表中的几个字段中获取值,并且工作正常。但我还希望有一个“@Description”,它是基于每行现有值的硬编码文本的串联。

DECLARE @Description VARCHAR(1000) = ''

SELECT t1.ID
      t1.myType
      t1.myName
      ,CASE WHEN 
      (
          t1.aValue1 = 1 
          AND t1.aValue2 = 1
          AND t2.aValue1 = 1
          AND t2.aValue2 = 1
      )
         THEN 'ELIGIBLE'
         ELSE 'NOT ELIGIBLE'
       END AS 'isEligible'

      ,@Description 
      -- Then I'm trying to set the description 
      /*
      IF    
          (
            t1.aValue1 = 2
          )
      SET @Description = @Description + ' t1.aValue1 is 2'
      IF
          (
            t1.aValue2 = 2
          )
      SET @Description = @Description + ' t1.aValue2 is 2'    
      IF
          (
            t2.aValue1 = 2
          )
      SET @Description = @Description + ' t2.aValue1 is 2'    
      IF
          (
            t2.aValue2 = 2
          )
      SET @Description = @Description + ' t2.aValue2 is 2'        
      */
      END AS 'Description'

  FROM [dbo].[Table1] t1
  JOIN [dbo].[Table2] t2 ON t1.ID = t2.ID

So for example, if a row had a t1.aValue1 = 2 and t2.aValue1 = 2 then the output might look something like this.

因此,例如,如果一行有t1.aValue1 = 2且t2.aValue1 = 2,那么输出可能看起来像这样。

ID | myType | myName | isEligible | Description
-----------------------------------------------
... 
24 | Red | John | Not Eligible | t1.aValue1 is 2 t2.aValue1 is 2
25 | Blue | Eric | Eligible | 
etc... 

I am using SSMS 2008 R2. How would I accomplish this?

我正在使用SSMS 2008 R2。我怎么做到这一点?

1 个解决方案

#1

You have the variable, but the expected results looks like you actually want it to the row? You can get it to the row just by using case, something like this:

你有变量,但预期结果看起来你真的想要它到行?你可以通过使用case来获取它,如下所示:

 END AS isEligible,
 case when t1.aValue1 = 2 then 't1.aValue1 is 2 ' else '' end +
 case when t1.aValue2 = 2 then 't1.aValue2 is 2 ' else '' end +
 case when t2.aValue1 = 2 then 't2.aValue1 is 2 ' else '' end +
 case when t2.aValue2 = 2 then 't2.aValue2 is 2 ' else '' end as Description