[sqlite] Transpose selected rows into columns
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 con
[sqlite] Transpose selected rows into columns
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] Transpose selected rows into columns
> On Apr 7, 2015, at 5:53 PM, John McKown > wrote: > > I'm an idiot, thanks for not pointing that out. I thought I was on the > PostgreSQL forum and managed to mess up. My apologies to all. We all have been there. I, for one, wish SQLite had some syntax sugar such as PIVOT/UNPIVOT: http://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php More handy than I care to admit :)
[sqlite] Transpose selected rows into columns
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 > 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.
[sqlite] Transpose selected rows into columns
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] Transpose selected rows into columns
?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 >
[sqlite] Transpose selected rows into columns
I'm an idiot, thanks for not pointing that out. I thought I was on the
PostgreSQL forum and managed to mess up. My apologies to all.
On Tue, Apr 7, 2015 at 9:19 AM, John McKown
wrote:
> OOPS, a slight mistake (incomplete)
>
> SELECT * FROM CROSSTAB('SELECT SerialNumber, Stim, Resp FROM table WHERE
> Stim in (''V0'',''V5'') ORDER BY 1, 2) AS ct(SerialNumber, V0, V5)
>
> On Tue, Apr 7, 2015 at 9:14 AM, John McKown
> wrote:
>
>> On Fri, Mar 27, 2015 at 10:48 AM, Drago, William @ CSG - NARDA-MITEQ <
>> William.Drago at l-3com.com> wrote:
>>
>>> All,
>>>
>>> Say I have a table that looks like this:
>>>
>>>
>>> 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
>>>
>>>
>>> I am presently using two SELECT statements as follows:
>>>
>>>
>>> SELECT SerialNumber, Resp AS V0 WHERE SerialNumber = '123' AND Stim =
>>> 'V0'
>>>
>>> SELECT SerialNumber, Resp AS V5 WHERE SerialNumber = '123' AND Stim =
>>> 'V5'
>>>
>>>
>>> And then reformatting the results using C# code before outputting to
>>> .csv file.
>>>
>>> Is there a way to do everything in SQL? I looked this up online and
>>> found some pretty complicated examples. Is it really that hard?
>>>
>>> Thanks,
>>> --
>>> Bill Drago
>>> Senior Engineer
>>>
>>
>>
>> ?I somehow missed this long ago. And so I may be too late. But have you
>> looked at the crosstab function in the tablefunc package?
>>
>> http://www.postgresql.org/docs/9.4/interactive/tablefunc.html?
>>
>>
>> ?SELECT * FROM CROSSTAB('SELECT SerialNumber, Stim, Resp FROM table WHERE
>> Stim in (''V0'',''V5'')) AS ct(SerialNumber, V0, V5)
>>
>> Note that the above is just my guess. I don't have any data to test with.
>> ?
>>
>>
>>
>> --
>> If you sent twitter messages while exploring, are you on a textpedition?
>>
>> He's about as useful as a wax frying pan.
>>
>> 10 to the 12th power microphones = 1 Megaphone
>>
>> Maranatha! <><
>> John McKown
>>
>
>
>
> --
> If you sent twitter messages while exploring, are you on a textpedition?
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>
--
If you sent twitter messages while exploring, are you on a textpedition?
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
[sqlite] Transpose selected rows into columns
OOPS, a slight mistake (incomplete)
SELECT * FROM CROSSTAB('SELECT SerialNumber, Stim, Resp FROM table WHERE
Stim in (''V0'',''V5'') ORDER BY 1, 2) AS ct(SerialNumber, V0, V5)
On Tue, Apr 7, 2015 at 9:14 AM, John McKown
wrote:
> On Fri, Mar 27, 2015 at 10:48 AM, Drago, William @ CSG - NARDA-MITEQ <
> William.Drago at l-3com.com> wrote:
>
>> All,
>>
>> Say I have a table that looks like this:
>>
>>
>> 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
>>
>>
>> I am presently using two SELECT statements as follows:
>>
>>
>> SELECT SerialNumber, Resp AS V0 WHERE SerialNumber = '123' AND Stim = 'V0'
>>
>> SELECT SerialNumber, Resp AS V5 WHERE SerialNumber = '123' AND Stim = 'V5'
>>
>>
>> And then reformatting the results using C# code before outputting to .csv
>> file.
>>
>> Is there a way to do everything in SQL? I looked this up online and found
>> some pretty complicated examples. Is it really that hard?
>>
>> Thanks,
>> --
>> Bill Drago
>> Senior Engineer
>>
>
>
> ?I somehow missed this long ago. And so I may be too late. But have you
> looked at the crosstab function in the tablefunc package?
>
> http://www.postgresql.org/docs/9.4/interactive/tablefunc.html?
>
>
> ?SELECT * FROM CROSSTAB('SELECT SerialNumber, Stim, Resp FROM table WHERE
> Stim in (''V0'',''V5'')) AS ct(SerialNumber, V0, V5)
>
> Note that the above is just my guess. I don't have any data to test with. ?
>
>
>
> --
> If you sent twitter messages while exploring, are you on a textpedition?
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>
--
If you sent twitter messages while exploring, are you on a textpedition?
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
[sqlite] Transpose selected rows into columns
On Fri, Mar 27, 2015 at 10:48 AM, Drago, William @ CSG - NARDA-MITEQ <
William.Drago at l-3com.com> wrote:
> All,
>
> Say I have a table that looks like this:
>
>
> 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
>
>
> I am presently using two SELECT statements as follows:
>
>
> SELECT SerialNumber, Resp AS V0 WHERE SerialNumber = '123' AND Stim = 'V0'
>
> SELECT SerialNumber, Resp AS V5 WHERE SerialNumber = '123' AND Stim = 'V5'
>
>
> And then reformatting the results using C# code before outputting to .csv
> file.
>
> Is there a way to do everything in SQL? I looked this up online and found
> some pretty complicated examples. Is it really that hard?
>
> Thanks,
> --
> Bill Drago
> Senior Engineer
>
?I somehow missed this long ago. And so I may be too late. But have you
looked at the crosstab function in the tablefunc package?
http://www.postgresql.org/docs/9.4/interactive/tablefunc.html?
?SELECT * FROM CROSSTAB('SELECT SerialNumber, Stim, Resp FROM table WHERE
Stim in (''V0'',''V5'')) AS ct(SerialNumber, V0, V5)
Note that the above is just my guess. I don't have any data to test with. ?
--
If you sent twitter messages while exploring, are you on a textpedition?
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
[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
[sqlite] Transpose selected rows into columns
> On 27 Mar 2015, at 3:48pm, Drago, William @ CSG - NARDA-MITEQ 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] Transpose selected rows into columns
All, Say I have a table that looks like this: 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 I am presently using two SELECT statements as follows: SELECT SerialNumber, Resp AS V0 WHERE SerialNumber = '123' AND Stim = 'V0' SELECT SerialNumber, Resp AS V5 WHERE SerialNumber = '123' AND Stim = 'V5' And then reformatting the results using C# code before outputting to .csv file. Is there a way to do everything in SQL? I looked this up online and found some pretty complicated examples. Is it really that hard? Thanks, -- Bill Drago Senior Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com 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] 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
