You might try
select SerialNumber, V0, V5, V5-V0
from
(select SerialNumber,
max(case Stim when 'V0' then Resp else null end) V0,
max(case Stim when 'V5' then Resp else null end) V5
from MyTable group by SerialNumber)
On 4/7/2015 6:58 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> Igor,
>
> Your solution works well. What I can't figure out is how to efficiently
> create a column representing V5-V0.
>
> SerialNumber | V0 | V5 | Vdiff
> -------------|-------|------|-------
> 123 | 0.2 | 0.6 | 0.4
>
>
> This is what I'm using, but it takes twice as long:
>
> select SerialNumber,
>
> max(case Stim when 'V0' then Resp else null end) V0,
> max(case Stim when 'V5' then Resp else null end) V5,
>
> (max(case Stim when 'V0' then Resp else null end) -
> max(case Stim when 'V5' then Resp else null end)) Vdiff
>
> from MyTable group by SerialNumber;
>
>
> There must be a more efficient way. (I tried V5-V0 and assigning intermediate
> values to variables but got nothing but errors.)
>
> Thanks,
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
>
>> -----Original Message-----
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
>> users-bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
>> Sent: Friday, March 27, 2015 3:20 PM
>> To: sqlite-users at mailinglists.sqlite.org
>> Subject: Re: [sqlite] Transpose selected rows into columns
>>
>> On 3/27/2015 11:48 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
>>> 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 SerialNumber,
>> max(case Stim when 'V0' then Resp else null end) V0,
>> max(case Stim when 'V5' then Resp else null end) V5 from MyTable
>> group by SerialNumber;
>>
>> --
>> Igor Tandetnik
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
> attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the event
> this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the U.S.Government.
> The recipient should check this e-mail and any attachments for the presence
> of viruses as L-3 does not accept any liability associated with the
> transmission of this e-mail. If you have received this communication in
> error, please notify the sender by reply e-mail and immediately delete this
> message and any attachments.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
