title: Best way to do a pivot table in SQLite?
body: <p>I'm using C# and SQLite to slice large amounts of data, and I often need to display my data in pivot table form. I can easily make my pivots dynamic by using C# to create the SQL command from another query, but I still can't decide which way to do the pivoting itself, so I would like to hear some opinions on that matter from programmers more experienced than me..</p>
  
  <p>I have three methods in mind. Lets say we have a simple table named tData with three columns: "row" represents the row number of that data,"col" represents the column number, and "val" represents the value.</p>
  
  <p>The orthodox method is to use CASE expressions:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
        row,
        sum(CASE col WHEN 1 THEN val END) AS col1,
        sum(CASE col WHEN 2 THEN val END) AS col2,
        sum(CASE col WHEN 3 THEN val END) AS col3
  FROM tData
  GROUP BY row
  </code></pre>
  
  <p>However, I was thinking maybe it could be faster if I ditch the CASE statements and use a logical expression directly on the value, utilizing the fact that true==1 and false==0:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
        row,
        sum((col=1)*val) AS col1,
        sum((col=2)*val) AS col2,
        sum((col=3)*val) AS col3
  FROM tData
  GROUP BY row
  </code></pre>
  
  <p>I suspect this method should be faster, since the CASE expression should have some overhead, but I'm not really sure.</p>
  
  <p>The third method is a bit more complex: it uses JOINs to do the pivoting:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
        rows.row,
        col1.valSum AS col1,
        col2.valSum AS col2,
        col3.valSum AS col3
  FROM
      (SELECT row FROM tData GROUP BY row) AS rows
  LEFT JOIN
      (SELECT row,sum(val) AS valSum FROM tData WHERE col=1 GROUP BY row) AS col1
      ON rows.row=col1.row
  LEFT JOIN
      (SELECT row,sum(val) AS valSum FROM tData WHERE col=2 GROUP BY row) AS col2
      ON rows.row=col2.row
  LEFT JOIN
      (SELECT row,sum(val) AS valSum FROM tData WHERE col=3 GROUP BY row) AS col3
      ON rows.row=col3.row
  </code></pre>
  
  <p>True, those JOINs have a serious overhead, but from my limited experience when dealing with large tables SQL implementations can do simple filter-group-and-sum operations much faster than custom-data-manipulation-on-each-row operations, and that more than makes up to that overhead.
  The problem is, those kind of SQL statements are more complex to generate, since each column appears in two places in the statement - once in the fields clause and once in the FROM clause, instead of just in the fields clause like the first two methods. Plus I need to be careful with all those temp table's names.</p>
  
  <p>So, any opinions?</p>