使用 SQL Server 2008 R2 的动态 case 语句

我有以下案例陈述，如下所示：

Example:

我有case statement:

case cola 
    when cola between '2001-01-01' and '2001-01-05' then 'G1'
    when cola between '2001-01-10' and '2001-01-15' then 'G2'
    when cola between '2001-01-20' and '2001-01-25' then 'G3'
    when cola between '2001-02-01' and '2001-02-05' then 'G4'
    when cola between '2001-02-10' and '2001-02-15' then 'G5'
    else '' 
end

Note：现在我想创建动态 case 语句，因为值日期和名称作为参数传递，并且可能会发生变化。

Declare @dates varchar(max) = '2001-01-01to2001-01-05,2001-01-10to2001-01-15,
                               2001-01-20to2001-01-25,2001-02-01to2001-02-05,
                               2001-02-10to2001-02-15'

Declare @names varchar(max) = 'G1,G2,G3,G4,G5'

变量中的值可能会根据要求而变化，它将是动态的。所以case语句应该是动态的而不使用循环。

我的糟糕尝试:

DECLARE @Name varchar(max)
DECLARE @Dates varchar(max)
DECLARE @SQL varchar(max)
DECLARE @SQL1 varchar(max)

SET @Name = 'G1,G2,G3,G4,G5'
SET @dates = '2001-01-01to2001-01-05,2001-01-10to2001-01-15,
              2001-01-20to2001-01-25,2001-02-01to2001-02-05,
              2001-02-10to2001-02-15'

SELECT @SQL =  STUFF((SELECT  ' ' + Value FROM 
(
SELECT 'WHEN Cola Between '''' AND '''' THEN ''' + A.Value + '''' AS Value 
FROM 
(
    SELECT 
        Split.a.value('.', 'VARCHAR(100)') AS Value  
        FROM  
        (
        SELECT CAST ('<M>' + REPLACE(@Name, ',',
            '</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,'') + ''

SET @SQL1 = 'CASE Cola '+@SQL+' ELSE '''' END'

PRINT(@SQL1);

Stuck: 但被卡住了@dates 2001-01-01to2001-01-05 into BETWEEN '2001-01-01' AND '2001-01-05'.

只需创建一个临时表（可以动态插入）并在LEFT JOIN. A LEFT JOIN（随着COALESCE) 占ELSE ''条件，但如果没有ELSE条件和所有范围都在数据中表示，INNER JOIN应该使用（并且不需要COALESCE).

为了从两个单独的变量动态填充临时表，这两个变量的数据仅按 CSV 列表中的位置对齐，其中之一是需要按逗号和字符串“to”拆分的二维数组，我使用 CTE（以便更轻松地拆分二维 @Dates 变量）和基于 SQLCLR 的字符串拆分器。我使用的分离器来自SQL# http://www.SQLsharp.com/库（我是该库的创建者，但此函数位于免费版本中），但您可以使用您喜欢的任何拆分器（但请不要使用基于 WHILE 循环的拆分器，因为这很愚蠢）。

CREATE TABLE #Cola
(
  StartDate DATETIME NOT NULL,
  EndDate DATETIME NOT NULL,
  Name NVARCHAR(50) NOT NULL
);

DECLARE @Dates VARCHAR(MAX) = '2001-01-01to2001-01-05,2001-01-10to2001-01-15,
                               2001-01-20to2001-01-25,2001-02-01to2001-02-05,
                               2001-02-10to2001-02-15';

DECLARE @Names VARCHAR(MAX) = 'G1,G2,G3,G4,G5';

-- dynamic population of temp table from two variables (@Dates being 2 dimensional)
;WITH cte AS
(
  SELECT vals.SplitNum,
         vals.SplitVal,
         CHARINDEX(N'to', vals.SplitVal) AS [WhereToSplit]
  FROM   SQL#.String_Split4k(@dates, ',', 1) vals
)
INSERT INTO #Cola (StartDate, EndDate, Name)
  SELECT CONVERT(DATETIME, SUBSTRING(cte.SplitVal, (cte.WhereToSplit - 10), 10)),
         CONVERT(DATETIME, SUBSTRING(cte.SplitVal, (cte.WhereToSplit + 2), 10)),
         names.SplitVal
  FROM cte
  INNER JOIN SQL#.String_Split4k(@names, ',', 1) names
          ON names.SplitNum = cte.SplitNum; -- keep the values aligned by position


SELECT tab.fields, COALESCE(cola.[Name], '') AS [Cola]
FROM   SchemaName.TableName tab
LEFT JOIN #Cola cola
        ON tab.cola BETWEEN cola.StartDate AND cola.EndDate