[sqlite] Transpose selected rows into columns

[Simon Slavin]

> On 27 Mar 2015, at 3:48pm, Drago, William @ CSG - NARDA-MITEQ <William.Drago 
> at L-3com.com> wrote:
> 
> SerialNumber | Stim  |  Resp
> -------------|-------|---------
> .            |  .    |   .
> .            |  .    |   .
> 123          |  V0   |  0.136
> 123          |  V1   |  0.201
> 123          |  V2   |  0.297
> 123          |  V3   |  0.311
> 123          |  V4   |  0.408
> 123          |  V5   |  0.599
> 124          |  .    |   .
> 124          |  .    |   .
> 
> 
> I want the rows containing V0 and V5 to become columns like this:
> 
> SerialNumber |  V0   |  V5
> -------------|-------|-------
> 123          | 0.136 | 0.599
> 124          | 0.126 | 0.587
> 125          | 0.119 | 0.602

SELECT T.SerialNumber, J0.Resp, J5.Resp FROM myTable AS T
        JOIN MyTable AS J0 ON (J0.SerialNumber = T.SerialNumber AND J0.Stim = 
'V0')
        JOIN MyTable AS J5 ON (J5.SerialNumber = T.SerialNumber AND J5.Stim = 
'V5');

This will be much faster if you have an index as follows or some equivalent:

CREATE INDEX m_SerStim ON myTable (SerialNumber, Stim);

Note, I have not tried the above code, it's just what I thought of.

Simon.