SQL Group By 直轉橫

Oracle SQL, MySQL, 通用的語法, 一般的group by 統計方式,為直列式由上而下逐筆顯示,
以下方式, 通常用於將特定已知的"資料",轉向作為[欄位]後,進行分析統計.
簡單的說, 就是原來直放的資料，變成橫放.
範例:
按各個userid, 橫向呈現於各個 CLASS_A,B,C 統計分析相關資料出現的次數.

SELECT user_id, sum(CLASS_A) AS CLASS_A,
                sum(CLASS_B) AS CLASS_B,
                sum(CLASS_C) AS CLASS_C
FROM ( select user_id,
              (CASE WHEN CLASS = 'a' THEN 1 ELSE '' END) AS CLASS_A,
              (CASE WHEN CLASS = 'b' THEN 1 ELSE '' END) AS CLASS_B,
              (CASE WHEN CLASS = 'c' THEN 1 ELSE '' END) AS CLASS_C
       from T1
     )
GROUP BY USER_ID
ORDER BY USER_ID;

簡化的作法,結果一樣....

SELECT user_id,
       sum(CASE WHEN CLASS = 'a' THEN 1 ELSE '' END) AS CLASS_A,
       sum(CASE WHEN CLASS = 'b' THEN 1 ELSE '' END) AS CLASS_B,
       sum(CASE WHEN CLASS = 'c' THEN 1 ELSE '' END) AS CLASS_C
FROM T1
GROUP BY USER_ID
ORDER BY USER_ID;

PS:若是ACCESS 資料庫, case when 換成iff 的語法即可,
如: SUM(IIF(CLASS = 'a', 1, 0))