Simon, Your solution almost works. It returns a lot of duplicate rows and I don't know how to fix that. I'm using what Igor posted.
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 Simon Slavin > Sent: Friday, March 27, 2015 12:20 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Transpose selected rows into columns > > > > 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. > _______________________________________________ > 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.
