?Why not CREATE an intermediate SQL VIEW or TABLE with V0 and V5 and then
use the resulting VIEW or TABLE as input to a second query that computes
the diff?
Or use R?
First query:
CREATE TABLE newtable
AS SELECT
?
max(case Stim when 'V0' then Resp else null end) V0,
max(case Stim when 'V5' then Resp else null end) V5;
or
CREATE VIEW newview AS SELECT... ;
Second query:
SELECT SerialNumber, V5, V0, (V5-V0) AS Vdiff FROM newtable;
Hope this helps.
Alternatively, R has transpose function, crosstabs and a good package for
going back and forth from tables to R dataframes.
Hadley Wickham's RSQLite 1.0.0
http://blog.rstudio.org/2014/10/25/rsqlite-1-0-0/
R and SQLite: Part 1
http://sandymuspratt.blogspot.com/2012/11/r-and-sqlite-part-1.html
Note crosstab is commonly used slang, formal statistical name is
"contingency table".
Converting between data frames and contingency tables
http://www.cookbook-r.com/Manipulating_data/Converting_between_data_frames_and_contingency_tables/
https://stat.ethz.ch/R-manual/R-patched/library/base/html/table.html
Transpose -- t()
http://www.r-statistics.com/tag/transpose/
http://www.statmethods.net/management/reshape.html
Hope this helps,
Jim Callahan
Orlando, FL
On Tue, Apr 7, 2015 at 9:58 AM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> 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
>
