Using CASE to create new column based on specific text in a string column

Viewed 8498

I found a couple similar threads, but none are working. I'm trying to create a new column for when another column satisfies a certain condition.

This is the code I'm working with:

SELECT DISTINCT R.[Column1] AS Person, 
SUM(CASE WHEN  R.[Event] = 'Event1' THEN 1 ELSE NULL END) AS Event1,
    CASE (WHEN L.[Column2] LIKE '%String1%' THEN 'String1'
        ELSE WHEN L.[Column2] LIKE '%String2%' THEN 'String2'
        ELSE WHEN L.[Column2] LIKE '%String3%' THEN 'String3'
        ELSE NULL END) AS NewColumn
  FROM [Database1].[dbo].[Table1] R
  LEFT JOIN
     [Database1].[dbo].[Table2] L
        ON R.[UniqueIdentifier] = L.[UniqueIdentifier]
    WHERE L.[Column2] LIKE '%String1%'
        OR L.[Column2] LIKE '%String2%'
        OR L.[Column2] LIKE '%String3%'
GROUP BY  R.[Column1], L.[Column2]
ORDER BY R.[Event1] DESC

If I take the CASE statements from column 2 out, then the query works fine. My desired results are three columns: Person, String, Event... counting Events with an aggregation on Person and String.

The error is: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'CASE'.

1 Answers
Related