Using CASE to create a new column based on specific text in a row column
I found a couple of similar threads, but none seem to work. I am trying to create a new column when another column meets a certain condition.
This is the code I am 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 accept CASE statements from column 2, then the query works fine. My desired results are three columns: Person, String, Event ... event counting with aggregation on Person and String.
Error: Msg 156, Level 15, State 1, Line 3 Invalid syntax near keyword "CASE".
+3
source to share
1 answer
You had some syntax problems:
First edition: CASE (WHEN
Second problem: ELSE WHEN
This should look fine now:
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'
WHEN L.[Column2] LIKE '%String2%'
THEN 'String2'
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;
+2
source to share