Re: [sqlite] Pivot & concat SELECT?

[Igor Tandetnik]

On 10/17/2016 10:32 AM, Daniel Polski wrote:



Den 2016-10-17 kl. 16:03, skrev Igor Tandetnik:

select unit, sum(1 << bit_position) from table1 where val group by unit;


Wow!!

Thanks alot!


Alot accepts expressions of gratitude but wonders why they are being 
addressed to it:

http://hyperboleandahalf.blogspot.com/2010/04/alot-is-better-than-you-at-everything.html

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[Andy Ling]

To add to the inefficiency :^)...

select unit, (sum(1 << bit_position) & 0xff) AS byte1, ((sum(1 << bit_position) 
>> 8) & 0xff) AS byte2 from table1 where val group by unit;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Daniel Polski
Sent: Mon 17 October 2016 15:47
To: SQLite mailing list
Subject: Re: [sqlite] Pivot & concat SELECT?


> select unit, sum(1 << bit_position) from table1 where val group by unit;
>

To make it more complex.. Is it possible to select into "different 
bytes" depending on bit_position? (For example that bit_position 0-7 
represent byte 1, bit_position 8-15 represent another)

To get a second "byte" I can tweak your initial idea to:

select unit, sum(1 << (bit_position-9)) from table1 where val and 
bit_position between 8 and 15 group by unit;

But that would need to get merged into the initial solution some way on 
the same select row result.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[R Smith]

Correction - Needs another bracket to read:

select  unit, (bit_position / 8) AS byteNo, (sum(1 << (bit_position % 
8))) AS byteVal from table1 where val group by unit, (bit_position / 8);



On 2016/10/17 5:04 PM, R Smith wrote:


select  unit, (bit_position / 8) AS byteNo, (sum(1 << (bit_position % 
8)) AS byteVal from table1 where val group by unit, (bit_position / 8);



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[R Smith]

On 2016/10/17 4:47 PM, Daniel Polski wrote:



select unit, sum(1 << bit_position) from table1 where val group by unit;



To make it more complex.. Is it possible to select into "different 
bytes" depending on bit_position? (For example that bit_position 0-7 
represent byte 1, bit_position 8-15 represent another)


To get a second "byte" I can tweak your initial idea to:

select unit, sum(1 << (bit_position-9)) from table1 where val and 
bit_position between 8 and 15 group by unit;


But that would need to get merged into the initial solution some way 
on the same select row result.



select  unit, (bit_position / 8) AS byteNo, (sum(1 << (bit_position % 
8)) AS byteVal from table1 where val group by unit, (bit_position / 8);



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[Simon Slavin]

On 17 Oct 2016, at 3:47pm, Daniel Polski  wrote:

> To make it more complex.. Is it possible to select into "different bytes" 
> depending on bit_position? (For example that bit_position 0-7 represent byte 
> 1, bit_position 8-15 represent another)

You can use the 'CASE' construction to make decisions like that.  See the 
section "The CASE expression" in



Not sure the specifics of including it in Igor's formula but I think it'd work.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[Daniel Polski]

Den 2016-10-17 kl. 16:40, skrev R Smith:


Luckily all data needed is in the table, and Igor's method will work 
just dandy in your case. Why not store the flags in full rather than 
per line though? You can easily manipulate the values by using 
standard bit-masking and boolean bit-wise functions in SQL.
Ex1. Set bit 3 of field fbit for unit 1:   UPDATE Tabel1 SET fbit 
= ((1 << 3) || fbit) WHERE unit = 1;
Ex2. Read bit 3 of field fbit for unit 1:SELECT (fbit && (1 << 3)) 
> 0 FROM Tabel1 WHERE unit = 1;


This way you can just read the fbit from a record in stead of having 
to go through all the grouping and aggregating to pivot it.


I can't manipulate the source data, so my idea is to create a mirror 
table with the bits already stored in "bytes". I intend to use the 
SELECT in a trigger on the source data table which synchronize the bits 
into bytes in the mirror table so I don't need to handle individual bits.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[Daniel Polski]

select unit, sum(1 << bit_position) from table1 where val group by unit;



To make it more complex.. Is it possible to select into "different 
bytes" depending on bit_position? (For example that bit_position 0-7 
represent byte 1, bit_position 8-15 represent another)


To get a second "byte" I can tweak your initial idea to:

select unit, sum(1 << (bit_position-9)) from table1 where val and 
bit_position between 8 and 15 group by unit;


But that would need to get merged into the initial solution some way on 
the same select row result.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[R Smith]

On 2016/10/17 3:54 PM, Daniel Polski wrote:


Let's say I have a table like this:

CREATE TABLE table1(
idINT,
unitINT,
bit_positionINT,
valBOOL
);

INSERT INTO table1 VALUES(1,1, 0, 1);
INSERT INTO table1 VALUES(2,1, 1, 1);
INSERT INTO table1 VALUES(3,1, 4, 1);
INSERT INTO table1 VALUES(4,1, 7, 1);

INSERT INTO table1 VALUES(5,2, 0, 1);

The "bit_position" represent individual bits in bytes, and "val" 
represent if the bit should be set or clear (unit 1= 0b10010011, unit 
2=0xb0001 for the data in the above table).


How can I get the byte data in a single select instead of parsing 
through the individual bits one by one?


Luckily all data needed is in the table, and Igor's method will work 
just dandy in your case. Why not store the flags in full rather than per 
line though? You can easily manipulate the values by using standard 
bit-masking and boolean bit-wise functions in SQL.
Ex1. Set bit 3 of field fbit for unit 1:   UPDATE Tabel1 SET fbit = 
((1 << 3) || fbit) WHERE unit = 1;
Ex2. Read bit 3 of field fbit for unit 1:SELECT (fbit && (1 << 3)) > 
0 FROM Tabel1 WHERE unit = 1;


This way you can just read the fbit from a record in stead of having to 
go through all the grouping and aggregating to pivot it.


10 points though for finding the most inefficient way to store 
nonvolatile bit-flags since magnetic core memory.

https://en.wikipedia.org/wiki/Magnetic-core_memory
:)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[Daniel Polski]

Den 2016-10-17 kl. 16:03, skrev Igor Tandetnik:
select unit, sum(1 << bit_position) from table1 where val group by unit; 


Wow!!

Thanks alot!

/Daniel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[Igor Tandetnik]

On 10/17/2016 9:54 AM, Daniel Polski wrote:

Let's say I have a table like this:

CREATE TABLE table1(
idINT,
unitINT,
bit_positionINT,
valBOOL
);

INSERT INTO table1 VALUES(1,1, 0, 1);
INSERT INTO table1 VALUES(2,1, 1, 1);
INSERT INTO table1 VALUES(3,1, 4, 1);
INSERT INTO table1 VALUES(4,1, 7, 1);

INSERT INTO table1 VALUES(5,2, 0, 1);

The "bit_position" represent individual bits in bytes, and "val"
represent if the bit should be set or clear (unit 1= 0b10010011, unit
2=0xb0001 for the data in the above table).

How can I get the byte data in a single select instead of parsing
through the individual bits one by one?


select unit, sum(1 << bit_position) from table1 where val group by unit;

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[Andy Ling]

On 17 Oct 2016, at 3:03pm, Igor Tandetnik  wrote:

> select unit, sum(1 << bit_position) from table1 where val group by unit;

Beat me to it. I was going to say

select unit, sum(val << bit_position) as byte from table1 group by unit;

But yours is slightly more efficient not summing the zeros (if there were any)

Andy
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot & concat SELECT?

[Simon Slavin]

On 17 Oct 2016, at 3:03pm, Igor Tandetnik  wrote:

> select unit, sum(1 << bit_position) from table1 where val group by unit;



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users