sql - Sqlite获取行中所有不同值的计数

Question

对于个人年终项目，我将出勤率从学校网站上剔除，希望对数据进行某种形式的可视化。我现在陷入了将数据转换成我需要的形式的麻烦。

目前我的数据库看起来像这样

Date,One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Dee
2014-09-03,P,P,P,P,AU,AU,P,T*,AU,P
2014-09-04,P,P,P,P,N/A,AU,P,T*,N/A,P
2014-09-05,P,P,P,P,AU,AU,P,P,P,P
2014-09-09,P,P,P,P,AU,AU,P,P,AU,P
2014-09-11,AU,AU,P,AU,AU,P,AU,AU,AU,P
2014-09-15,P,P,P,P,AU,P,P,P,AU,P
2014-09-17,P,P,P,P,AU,AU,P,P,AU,P

这些栏是每个时期，每个栏都有我的存在的指示器。我的问题是，是否可以仅使用sqlite将其转换为类似内容？

Date,P,AU,T*,N/A
2014-09-03,6,3,1,0
2014-09-04,6,1,1,2
2014-09-05,8,2,0,0
2014-09-09,7,3,0,0
2014-09-11,3,7,0,0
2014-09-15,8,2,0,0
2014-09-17,7,3,0,0
2014-09-19,9,1,0,0

计算行中值的每次出现。

Answer 1

像这样：

select date,
       case when one = 'p' then 1 else 0 end +
       case when two = 'p' then 1 else 0 end +
       ...
       case when dee = 'p' then 1 else 0 end as p,


       case when one = 'au' then 1 else 0 end +
       case when two = 'au' then 1 else 0 end +
       ...
       case when dee = 'au' then 1 else 0 end as au,

       ...
from table