使用CASE根据字符串列中的特定文本创建新列 [英] Using CASE to create new column based on specific text in a string column

问题描述

我发现了几个类似的线程，但是没有一个在工作.我正在尝试为另一列满足特定条件的情况创建一个新列.

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

如果我从第2列中取出CASE语句，则查询工作正常.我希望得到的结果是三列:人员，字符串，事件...通过对人员和字符串进行汇总来对事件进行计数.

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.

错误是:消息156，级别15，状态1，第3行附近的语法不正确 关键字"CASE".

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

推荐答案

您遇到了一些语法问题:

You had some syntax issues:

第一期是:CASE (WHEN

第二个问题是:ELSE WHEN

现在应该可以正常运行了:

This should run 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;

这篇关于使用CASE根据字符串列中的特定文本创建新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！