[sqlite] Enhancement Request: sqlite3 shell output mode "insert" include column names
Joe, Seems to work. My use case is piping sqlite3 to sqlite3 with something other than 'SELECT *'. Thanks ! Roy Keene On 04/07/2015 04:21 PM, Joe Mistachkin wrote: > Roy Keene wrote: >> The "insert" output mode is very basic, however, and lacks column >> names in the output. >> > Can you try the changes on the "expShell" branch and see if they work > for your use case? In order to get column names, you'll need to set > the headers to "on". > > -- > Joe Mistachkin > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Different timings on min() and max()
On 7 Apr 2015, at 9:42pm, Paul Caskey wrote: > Any idea why there is such a performance hit when I ask for both min() and > max() at the same time? Shouldn't it be just as fast as querying them > individually? Did you try using EXPLAIN QUERY PLAN ? If the column you're minning (or maxing) is indexed, SQLite can find the row it needs just by checking one end of one index. It can go straight there. If you're asking for both min() and max() I'm not sure what it does. It might be try to find both ends of the index. Or maybe it has to do two complete searches. Simon.
[sqlite] error during sqlite_bind
On 4/7/15, Igor Tandetnik wrote: > On 4/7/2015 9:11 PM, Kumar Suraj wrote: >> You can add this to top of the code.. >> >> char command[512]; >> snprintf(command, 512, SELECT_DN); > > I don't see anything wrong in the code you've shown. The problem must > lie in the code you haven't. My guess would be, the query you are > preparing is not the query you think you are preparing. Print "command" > and strlen(command) right before sqlite3_prepare_v2 call, to double-check. Or, print the result of sqlite3_sql(newStmt) right after sqlite3_prepare_v2() returns successfully. -- D. Richard Hipp drh at sqlite.org
[sqlite] error during sqlite_bind
On 4/7/2015 9:11 PM, Kumar Suraj wrote: > You can add this to top of the code.. > > char command[512]; > snprintf(command, 512, SELECT_DN); I don't see anything wrong in the code you've shown. The problem must lie in the code you haven't. My guess would be, the query you are preparing is not the query you think you are preparing. Print "command" and strlen(command) right before sqlite3_prepare_v2 call, to double-check. -- Igor Tandetnik
[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] error during sqlite_bind
On 4/7/2015 8:04 PM, Kumar Suraj wrote: > if ( (rv = sqlite3_prepare_v2(sqlHandle->db, command, strlen(command), > &newStmt, NULL) ) != SQLITE_OK ) command: undeclared identifier. -- Igor Tandetnik
[sqlite] error during sqlite_bind
You can add this to top of the code.. char command[512]; snprintf(command, 512, SELECT_DN); On Tue, Apr 7, 2015 at 6:11 PM, Kumar Suraj wrote: > I have not added the whole code.. command is defined.. in my code.. > > On Tue, Apr 7, 2015 at 5:42 PM, Igor Tandetnik wrote: > >> On 4/7/2015 8:04 PM, Kumar Suraj wrote: >> >>> if ( (rv = sqlite3_prepare_v2(sqlHandle->db, command, strlen(command), >>> &newStmt, NULL) ) != SQLITE_OK ) >>> >> >> command: undeclared identifier. >> >> -- >> Igor Tandetnik >> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >
[sqlite] error during sqlite_bind
I have not added the whole code.. command is defined.. in my code.. On Tue, Apr 7, 2015 at 5:42 PM, Igor Tandetnik wrote: > On 4/7/2015 8:04 PM, Kumar Suraj wrote: > >> if ( (rv = sqlite3_prepare_v2(sqlHandle->db, command, strlen(command), >> &newStmt, NULL) ) != SQLITE_OK ) >> > > command: undeclared identifier. > > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] error during sqlite_bind
Hi
I am trying to use SQLite c api to create a db, table and then do a select
query but i am getting following error
#define SQLITE_RANGE 25 /* 2nd parameter to sqlite3_bind out of range */
Here is the code call
#define SELECT_DN "select kindex from TBL where dn=?"
char buffer[100] = "a/b/c/d";
char * testdn = buffer;
lBuffer.size = 100;
lBuffer.ptr = testdn;
if ( (rv = sqlite3_prepare_v2(sqlHandle->db, command, strlen(command),
&newStmt, NULL) ) != SQLITE_OK )
{
fprintf(stderr, "Error Insert : sqlite3_prepare_v2, Error code :
%d\n", rv);
return;
}
rv = sqlite3_bind_blob(newStmt, 1, lBuffer.ptr, lBuffer.size,
SQLITE_STATIC);
if (rv != SQLITE_OK)
{
fprintf(stderr, "Error Insert : sqlite3_bind_blob, Error code :
%d\n", rv);
return;
}
[sqlite] fts5
On Thu, Sep 11, 2014 at 8:58 AM, Dan Kennedy wrote:
> Fts5 is still in the experimental stage at the moment.
>
> If anybody has any ideas for useful features, or knows of problems with FTS4
> that could be fixed in FTS5, don't keep them to yourself!
Apologies for not noticing this thread earlier!
After fts2 was released, someone engaged me on a discussion about
whether I had considered an alternate storage strategy. The current
system of {term,doclist} where doclist is something like
[{docid,[pos]}] means that the index b-tree is very lumpy because
doclists are (extremely) variably-sized. The suggestion was to store
things as an ordered set of {term,doc,pos} tuples, then use some sort
of delta encoding between them. This would quite naturally balance
the interior of the index versus the leaves, and would also work well
with incremental merging since you only needed to worry about the head
block for each segment being scanned. I believe the current fts5 code
gets similar results by keeping an index for large doclists to allow
quickly scanning to the right point, so this might not add much.
Something that bugged me a lot was that I had used deletion markers to
cancel out hits, but did not provide a way for deletion markers to
cancel out. The main problem with this was that a large delete would
stay in the system until it reached the final segment, even if it had
already overtaken all of the original inserts. I wished that I had
either maintained a separate structure tracking _document_ deletion
(which would make merges somewhat more complicated because they
wouldn't be term-centric), or code updates as "delete+insert". In the
latter case deletes could drop out at the point where they reached the
original insert.
I seem to recall being upset by the amount of compression gzip could
manage against index blocks, even though they mostly aren't very
large. I think things got around 1/4 or 1/3 smaller. To me that
implied that there were probably some gains to be had in encoding.
[This is distinct from compression of content data, which fts3/4
already support.]
I'm 100% convinced that merging could be improved :-). Clearly there
is a lot of benefit to merging together the low-order segments, but I
never figured out a good way to model whether merging the larger
segments actually improved anything, since at some point you no longer
can really enforce locality anyhow. But I'm guessing that your
experiments with the sqlite4 key/value store probably involve lots of
exploration along these lines.
-scott
[sqlite] Different timings on min() and max()
Hello, Any idea why there is such a performance hit when I ask for both min() and max() at the same time? Shouldn't it be just as fast as querying them individually? Nothing else is reading or writing to this data at this time. [14:32 sql04:/opt/pcaskey]$ cat ~/.sqliterc .output /dev/null PRAGMA foreign_keys = ON; PRAGMA synchronous = OFF; PRAGMA journal_mode = OFF; PRAGMA locking_mode = EXCLUSIVE; PRAGMA cache_size = -2000; .output stdout [14:33 sql04:/opt/pcaskey]$ [14:33 sql04:/opt/pcaskey]$ sqlite3 star2star.db -- Loading resources from /export/home/pcaskey/.sqliterc SQLite version 3.6.20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .timer on sqlite> select min(dtime) from intstats; 1416441600 CPU Time: user 0.001000 sys 0.00 sqlite> select max(dtime) from intstats; 1426999800 CPU Time: user 0.00 sys 0.00 sqlite> select min(dtime), max(dtime) from intstats; 1416441600|1426999800 CPU Time: user 221.806280 sys 47.434789 sqlite> sqlite> select count(*) from intstats; 607009116 CPU Time: user 6.341036 sys 20.271918 sqlite> sqlite> Thanks, Paul
[sqlite] Enhancement Request: sqlite3 shell output mode "insert" include column names
Roy Keene wrote: > > The "insert" output mode is very basic, however, and lacks column > names in the output. > Can you try the changes on the "expShell" branch and see if they work for your use case? In order to get column names, you'll need to set the headers to "on". -- Joe Mistachkin
[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] Different timings on min() and max()
On Tue, Apr 7, 2015 at 1:42 PM, Paul Caskey wrote: > Any idea why there is such a performance hit when I ask for both min() and > max() at the same time? Shouldn't it be just as fast as querying them > individually? This has come up before. Either alone can be satisfied from an index lookup, which seems likely in this case given your count(*) results, but both together aren't optimized. http://www.sqlite.org/optoverview.html#minmax You could do SELECT (SELECT min(dtime) FROM intstats), (SELECT max(dtime) FROM intstats). Or go poking through the archives for other past examples. -scott
[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] Enhancement Request: sqlite3 shell output mode "insert" include column names
All, The current SQLite3 shell has a ".mode" directive to set the output mode. One such mode is called "insert" which is intended to generate INSERT statements. The "insert" output mode is very basic, however, and lacks column names in the output. Attached is a patch to include the column names. This patch works for my dataset, but I am not familiar enough with SQLite to know if it is correct. This addresses also a request on StackOverflow: http://stackoverflow.com/questions/4199850/sqlite-export-with-column-names It always outputs the column name, regardless of the ".headers" directive. Thanks, Roy Keene
