How to specify multiple values in when using case statement in sql server

Question

I have the following table

Id  Number  TypeOfChange
1   2X      Scope,Cost,Schedule,EVM,PA
2   3x      Scope,Cost

Expected output:

Id  Number  TypeOfChange                Scope  Cost  Schedule  EVM  PA
1   2X      Scope,Cost,Schedule,EVM,PA  X      X     X         X    X
2   3x      Scope,Cost                  X      X

I try the following script but its not working

SELECT 
    Id, 
    Number,
    TypeOfChange,        
    Scope = CASE 
        WHEN TypeOfChange = 'Scope' THEN 'X' 
        ELSE '' END,
    Cost = CASE 
        WHEN TypeOfChange = 'Cost' THEN 'X' 
        ELSE '' END,
    Schedule = CASE 
        WHEN TypeOfChange = 'Schedule' THEN 'X' 
        ELSE '' END,
    EVM = CASE 
        WHEN TypeOfChange = 'EVM' THEN 'X' 
        ELSE '' END,
    PA = CASE 
        WHEN TypeOfChange = 'PA' THEN 'X' 
        ELSE '' END 
FROM A

Answer 1

I think, using LIKE is wrong approach. Especcialy in cases, when one of your strings become f.e."Periscope". You will get false positives.

Try to create user defined function to split strs:

CREATE FUNCTION [dbo].[str__split](
     @str           NVARCHAR(MAX)
    ,@delimiter     NVARCHAR(MAX)
)
RETURNS @split TABLE(
     [str] NVARCHAR(MAX)
)
AS
BEGIN

    INSERT INTO @split(
         [str]
    )
    SELECT 
        [X].[C].[value]('(./text())[1]', 'nvarchar(4000)')
    FROM 
        ( 
            SELECT 
                [X] = CONVERT(XML, '<i>' + REPLACE(@str, @delimiter, '</i><i>') + '</i>').query('.')
        )                   AS  [A] 
    CROSS APPLY 
        [X].[nodes]('i')    AS  [X]([C]);

   RETURN;

END

And then use query:

SELECT
     [t].*
    ,[Scope]        =   CASE WHEN [t2].[Scope]      IS NULL THEN NULL ELSE 'X' END
    ,[Cost]         =   CASE WHEN [t2].[Cost]       IS NULL THEN NULL ELSE 'X' END
    ,[Schedule]     =   CASE WHEN [t2].[Schedule]   IS NULL THEN NULL ELSE 'X' END
    ,[EVM]          =   CASE WHEN [t2].[EVM]        IS NULL THEN NULL ELSE 'X' END
    ,[PA]           =   CASE WHEN [t2].[PA]         IS NULL THEN NULL ELSE 'X' END
FROM
    [your table]    AS [t]
OUTER APPLY
    (
        SELECT * FROM (SELECT [str] from [dbo].[str__split]([TypeOfChange], ','))   AS [d]
        PIVOT
        (MAX([str]) FOR [str] IN ([Scope], [Cost], [Schedule], [EVM], [PA]))        AS [piv]
    )   AS [t2]

Answer 2

If TypeOfChange is a dynamic value, you may want to go the dynamic route.

select * into [T1] from 
(values (1, '2X', 'Scope,Cost,Schedule,EVM,PA'), (2, '3x', 'Scope,Cost'), (3, '4x', 'someOtherType')) t(Id, Number, TypeOfChange) 

--typeOfChange into column list
Declare @SQL varchar(max)  = Stuff(( 
    SELECT distinct ',' + QuoteName(LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))))
    FROM ( SELECT CAST('<XMLRoot><RowData>' + REPLACE(TypeOfChange,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
    FROM   [T1]) t CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
    Order by 1 For XML Path('')),1,1,'')

Select  @SQL = '
Select [Id],[Number], [TypeOfChange],' + @SQL + '
From (
        SELECT Id, Number, TypeOfChange, 
                LTRIM(RTRIM(m.n.value(''.[1]'',''varchar(8000)''))) AS [Type], ''X'' as Value 
                FROM (  SELECT Id, Number, TypeOfChange, CAST(''<XMLRoot><RowData>'' + REPLACE(TypeOfChange,'','',''</RowData><RowData>'') + ''</RowData></XMLRoot>'' AS XML) AS x
                        FROM   [T1]) t CROSS APPLY x.nodes(''/XMLRoot/RowData'')m(n)
     ) A
 Pivot (max(Value) For [Type] in (' + @SQL + ') ) pvt'
Exec(@SQL);

Alternatively you may want to define your Types in a lookup table

select * into [Types] from 
(values (1, 'Scope'), (2, 'Cost'), (3, 'Schedule'), (4, 'EVM'), (5, 'PA'), (6, 'someOtherType')) a (Id, TypeOfChange) 

Then change the above --typeOfChange into column.. block like this:

--typeOfChange into column list
Declare @SQL varchar(max)  = Stuff(( 
    SELECT distinct ',' + QuoteName(TypeOfChange)
    FROM [Types] 
    Order by 1 For XML Path('')),1,1,'')

Answer 3

we can try charindex or patindex

 SELECT 
        Id, 
        Number,
        TypeOfChange,        
        Scope = CASE 
            WHEN CHARINDEX('Scope',TypeOfChange)>0   THEN 'X' 
            ELSE '' END,
        Cost = CASE 
            WHEN  CHARINDEX('Cost',TypeOfChange)>0   THEN 'X'  
            ELSE '' END,
        Schedule = CASE 
            WHEN  CHARINDEX('Schedule',TypeOfChange)>0   THEN 'X' 
            ELSE '' END,
        EVM = CASE 
            WHEN  CHARINDEX('EVM',TypeOfChange)>0   THEN 'X' 
            ELSE '' END,
        PA = CASE 
               WHEN  CHARINDEX('PA',TypeOfChange)>0   THEN 'X' 
            ELSE '' END  
    FROM #AA

output

Id  Number  TypeOfChange    Scope   Cost    Schedule    EVM PA
1   2X  Scope,Cost,Schedule,EVM,PA  X   X   X   X   X
2   3x  Scope,Cost  X   X           

Answer 4

Use Like operator.

SELECT 
    Id, 
    Number,
    TypeOfChange,        
    Scope = CASE 
        WHEN TypeOfChange Like '%Scope%' THEN 'X' 
        ELSE '' END,
    Cost = CASE 
        WHEN TypeOfChange Like '%Cost%' THEN 'X' 
        ELSE '' END,
    Schedule = CASE 
        WHEN TypeOfChange Like '%Schedule%' THEN 'X' 
        ELSE '' END,
    EVM = CASE 
        WHEN TypeOfChange Like '%EVM%' THEN 'X' 
        ELSE '' END,
    PA = CASE 
        WHEN TypeOfChange Like '%PA%' THEN 'X' 
        ELSE '' END  
FROM A

Example: