SQLITE 将大量行转换为列 [英] SQLITE transpose large number of rows into columns

问题描述

我有一个这样订购的数据库:

I have a database ordered like so:

    ID     |    Subject     |      Value
 ---------------------------------------------
     1            Subj1            Val1
     1            Subj2            Val2
     2            Subj1            Val3
     2            Subj5            Val4

等等.ID 数以千计，主体数以万计.

And so on. IDs number in the thousands, and the subjects in tens of thousands.

我想找到这个矩阵的转置.我怎么做?

I want to find the transpose of this matrix. How do I do that?

如果主题是一个小的静态集合，那么我可以使用 CASE 语句，如本解决方案中所示:SQLITE - 正确地将行转换为列，但在我的情况下，主题是一个大型的动态集，因此除非我在数据库外的某些应用程序中动态构建 SQL，否则 CASE 将无法工作.就我而言，没有应用程序"；正如我在下面的评论中提到的，我正在寻找纯 SQL 解决方案.

If the subjects were a small, static set, then I could use CASE statements as in this solution: SQLITE - transposing rows into columns properly, but in my situation, subjects are a large, dynamic set, so CASE won't work unless I'm building the SQL dynamically in some application outside the database. In my case, there is no "application"; as I mention in a comment below, I'm looking for a pure-SQL solution.

关键在于:我正在使用 SQLite，但它缺少使这更容易的 PIVOT 语句.我不知道是否还有办法做到这一点，并且没有教过很多过去的连接类型.对于较小的数据库，我会尝试一系列左连接，但由于我的结果中有很多列值，我不知道如何.

Here's the kicker: I'm using SQLite, and it's missing the PIVOT statement that would make this easier. I don't know if there's still a way to do this, and haven't been taught much past types of joins. For a smaller database I would have tried a series of left joins, but since I have so many column values in my result I don't know how.

如何转换为这种形式?

      ID      |     Subj1       |        Subj2      |    Subj3     |       etc.
   --------------------------------------------------------------------
      1              Val1                  Val2            0        
      2              Val3                   0              0 

推荐答案

您可以使用条件聚合来透视您的数据:

You can use conditional aggregation for pivoting your data:

select id,
    max(case when Subject = 'Subj1' then Value end) as Subj1,
    max(case when Subject = 'Subj2' then Value end) as Subj2,
    max(case when Subject = 'Subj3' then Value end) as Subj3,
    . . . 
from your_table
group by id;

这里注意，如果有多行id相同，subject相同，则id只返回最大值的一行.

Note here that if there are multiple rows with same id and same Subject, only one row with max value will be returned for the id.

这篇关于SQLITE 将大量行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！