动态SQL / SQL循环/动态选择语句

Question

我昨晚发了一个很长的问题，太深入了。因此，让我们保持这个更具理念性。

假设你有一个非常长的SELECT语句，它由几十个代码块组成。每个块创建的计算列略有不同。为了简单起见，您以一种颜色命名了每个块。

在顶部还声明了5个变量。每个变量都包含一个字符串形式的颜色。

是否可以遍历这些变量并使用其中的值来选择要运行的SELECT语句中的哪些块？

因此，查询将返回3个计算列。绿色列、黄色列，然后又是绿色列，其他变量4和5均为空

DECLARE

@variable1 = 'Green'
@variable2 = 'Yellow'
@variable3 = 'Green'
@variable4 =  NULL
@variable5 = NULL

SELECT

CASE WHEN... THEN... AS 'Red Column',
CASE WHEN... THEN... AS 'Yellow Column',
CASE WHEN... THEN... AS 'Pink Column',
CASE WHEN... THEN... AS 'Green Column',
CASE WHEN... THEN... AS 'Orange Column',
CASE WHEN... THEN... AS 'Purple Column',
CASE WHEN... THEN... AS 'Blue Column'

FROM Some_tables

WHERE something = something

ORDER BY

使用SQL能做到这一点吗？非常感谢你提前

Answer 1

可以通过两个步骤来完成: 1.将所有数据插入到中间表Tmp1中

SELECT
CASE WHEN... THEN... AS 'Red Column',
CASE WHEN... THEN... AS 'Yellow Column',
CASE WHEN... THEN... AS 'Pink Column',
CASE WHEN... THEN... AS 'Green Column',
CASE WHEN... THEN... AS 'Orange Column',
CASE WHEN... THEN... AS 'Purple Column',
CASE WHEN... THEN... AS 'Blue Column'
Into Tmp1 
FROM Some_tables

动态选择表单Tmp1表

@sql = 'select '
if(@variable1 is not null)
begin 
  @sql= @sql + @variable1  + ' , '
end 

if(@variable2 is not null)
begin 
  @sql= @sql + @variable2  + ' , '
end 
 .......
.......
@sql = left(@sql ,len(@sql)-1)  -- this to remove last ","
@sql = @sql  + ' from Tmp1'