You are welcome. The crosstab followed by a calculation is a common pattern in statistics, political science and accounting (if you added a percent change column after the diff you would have the classic "accounting variance" report).
Using an intermediate TABLE or VIEW is an "information hiding" tactic analogous to putting a complex calculation in a subroutine. Jim Callahan Orlando, FL On Wed, Apr 8, 2015 at 1:36 AM, Drago, William @ CSG - NARDA-MITEQ < William.Drago at l-3com.com> wrote: > Jim, > > This works quite well. Thank you. > > And thanks to all others who replied. > > -- > 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 Jim Morris > > Sent: Tuesday, April 07, 2015 10:07 AM > > To: sqlite-users at mailinglists.sqlite.org > > Subject: Re: [sqlite] Transpose selected rows into columns > > > > 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 > > > > 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 >
