Динамический регистр с использованием SQL Server 2008 R2

Question

У меня есть следующее утверждение, как показано ниже:

Пример:

у меня есть 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

Примечание. Теперь я хочу создать динамический оператор 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);

Застрял: Но застрял, чтобы разделить @dates2001-01-01to2001-01-05 в BETWEEN '2001-01-01' AND '2001-01-05',

Answer 1

Просто создайте временную таблицу (которая может быть вставлена ​​в динамически) и используйте ее в LEFT JOIN, LEFT JOIN (вместе с COALESCE) приходится ELSE '' состояние, но если бы не было ELSE условия и все диапазоны были представлены в данных, INNER JOIN следует использовать (и не нужно COALESCE).

Чтобы динамически заполнить временную таблицу из двух отдельных переменных, чьи данные выровнены только по позиции в списке CSV, и одна из которых представляет собой двумерный массив, который необходимо разделить на запятую и строку "to", я использовал CTE (чтобы было проще разделить двумерную переменную @Dates) и разделитель строк на основе SQLCLR. Я использовал сплиттер из библиотеки SQL# (создателем которого я являюсь, но эта функция есть в бесплатной версии), но вы можете использовать любой сплиттер, который вам нравится (но, пожалуйста, не используйте сплиттер на основе цикла 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