title: SQLite split column on another column's value
body: <p>I have a database in which multiple variables appear as consecutive rows (shown below, variables differentiated by their tags). As a result, their values appear as consecutive rows in the 'value' column.</p>
  <p>Existing table:</p>
  <p><img src="https://i.stack.imgur.com/AVntT.png" alt="Existing table" /></p>
  <p>For data analysis, I need to split each variable's value into separate columns, as illustrated below.</p>
  <p><img src="https://i.stack.imgur.com/QxS5b.png" alt="Desired layout" /></p>
  <p>The SQLite query is listed below. I have tried using GROUP BY tag and averaging the values, but the result becomes too granular to be useful.</p>
  <p>Advice would be much appreciated!</p>
  <pre><code>SELECT
          tag
          ,time
          ,value
  FROM 
      [archive]..[interp]
  WHERE 
      tag 
  IN
      ( 'flow1.Val'           
       , 'flow2.Val'
       , 'density1.Val' 
       , 'density2.Val'
       )
    
  AND 
      time 
  BETWEEN 
      't-1d' and 't'
  AND 
      timestep = '1h'
  </code></pre>