Dynamic case statement using SQL Server 2008 R2

Question

I have the following case statement, as shown below:

Example:

I have a 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 : now I want to create a dynamic case statement due to the date and name values ​​passed as a parameter, and it may change.

 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' 

The values ​​in the variables can change in accordance with the requirements, they will be dynamic. Therefore, the case statement must be dynamic without using a loop.

My bad attempt :

 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 : but stuck to share @dates 2001-01-01to2001-01-05 in BETWEEN '2001-01-01' AND '2001-01-05' .

Answer 1

Just create a temporary table (which can be inserted dynamically) and use it in the LEFT JOIN . A LEFT JOIN (together with COALESCE ) takes into account the ELSE '' condition ELSE '' , but if there was no ELSE condition and all ranges were represented in the data, use INNER JOIN (and do not need COALESCE ).

To dynamically populate a temporary table of two separate variables, the data of which is aligned only by position in the CSV list, and one of which is a two-dimensional array, which must be divided both by comma and by string, I used CTE (to simplify splitting the two-dimensional variable @Dates) and the line separator based on SQLCLR The splitter I used is from the SQL # library (which I am the creator, but this function is in the free version), but you can use any splitter you like (but please do not use the WHILE loop based separator, as this is just stupid).

 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