sql - 计算行SQL Server中出现次数最多的单词(Count most occurring word in row SQL Server)

Question

I'm trying to get the number of times a certain word occur in a query row.

(我正在尝试获取某个单词在查询行中出现的次数。)

For example :

(例如 ：)

Name   | Chemistry | Physics   | Biology   | Maths
-------+-----------+-----------+-----------+--------
John   | Excellent | Good      | Good      | Poor
Kelvin | Excellent | Excellent | Excellent | Poor 

I want to get something for each row like

(我想为每一行得到一些东西)

Name   | Excellent | Good | Poor
-------+-----------+------+-------
John   |    1      |   2  |   1
Kelvin |    3      |   0  |   1

  ask by Assurance Femi translate from so

Answer 1

Just add them up using case expressions:

(只需使用case表达式将它们加起来即可：)

select name,
       (case when chemistry = 'Excellent' then 1 else 0 +
        case when physics = 'Excellent' then 1 else 0 +
        case when biology = 'Excellent' then 1 else 0 +
        case when math = 'Excellent' then 1 else 0
       ) as num_excellents,
       . . .
from t;

A fancier method would use apply and aggregation:

(更好的方法将使用apply和Aggregation：)

select t.name, v.*
from t cross apply
     (select sum(case when marks = 'Excellent' then 1 else 0 end) as excellent,
             sum(case when marks = 'Good' then 1 else 0 end) as good,
             sum(case when marks = 'Poor' then 1 else 0 end) as Poor      
      from (values (chemistry), (physics), (biology), (math)
           ) v(marks);