如何在 SQLite 中透視數據

我有一個包含文本數據的表，其中包含一堆不同語言的翻譯。每個翻譯都是針對特定標籤的。

我需要生成一個數據透視表，以便快速獲取缺少的內容。

記錄的一個例子是

1, en, hello
1, fr, bonjour
1, es, hola
2, en, how are you
2, fr, 
3, es, come es stas

儘管所有語言的翻譯都應該始終存在，但我不是 100% 肯定是這種情況。因此必須考慮缺少的欄位。

想要的結果是這樣

|ID|EN|FR|ES|
|1|hello|bonjour|hola|
|2|how are you| |come es stas|

我面臨的挑戰是填充數據庫時列順序可能並不總是相同，所以理論上我應該有一個動態的欄位列表。

SQLite 中沒有直接的 PIVOT 函式，所以我開始嘗試使用 group_concat 獲取逗號分隔的字元串。

SELECT DISTINCT language, group_concat(word, ',') OVER (PARTITION BY language) AS group_concat
FROM vocabulary;

如果需要，我可以稍後在 Python 中執行結果；問題是任何缺失值都不會附加一個空項，從而將所有串聯移動 n，從而使該解決方案無效。

我還嘗試在 select 謂詞中使用 filter 子句（儘管這意味著對列進行硬編碼），但我未能成功。

關於如何實現這一點的任何想法？

以下查詢將返回不包含所有翻譯的所有 ID：

SELECT id, COUNT(*) AS count
FROM vocabulary
GROUP BY id
HAVING count < (SELECT COUNT(DISTINCT language)
               FROM vocabulary);

如果您想知道每個 ID 缺少哪些語言，請使用複合查詢來查找在所有語言列表中但不在此 ID 的語言中的那些語言：

WITH missing_ids AS (
 SELECT id, COUNT(*) AS count
 FROM vocabulary
 GROUP BY id
 HAVING count < (SELECT COUNT(DISTINCT language)
                 FROM vocabulary)
)
SELECT id,
      (SELECT group_concat(language)
       FROM (SELECT DISTINCT language FROM vocabulary
             EXCEPT
             SELECT language FROM vocabulary WHERE id = missing_ids.id)
      ) AS missing_languages
FROM missing_ids;

我能夠得到一個半令人滿意的解決方案，儘管唯一缺少的是動態列選擇。

SELECT id, 
      MAX(CASE WHEN "language" == 'it' THEN word END) as 'it',
      MAX(CASE WHEN "language" == 'en' THEN word END) as 'en',
      MAX(CASE WHEN "language" == 'ru' THEN word END) as 'ru'
FROM (select t.*,
            row_number() over (partition by language order by id) as seq
     from vocabulary t
    ) t
GROUP BY t.seq;

遺憾的是 SQLite 不支持動態查詢，所以這些必須通過被呼叫者建構，在我的例子中是 Python 腳本。

引用自：https://dba.stackexchange.com/questions/266614