1

I have a column of data in a SQLite database indicating which sensor data is included in that row. The problem is that there can be multiple sensors updated at a time, delimited by | and in no guaranteed order.

What I have in my table Data:

*-------*----------------------*
* RowID * Sensor               *
*-------*----------------------*
*    1  * 'EEG|Eyetracker|GSR' *
*    2  * 'Eyetracker|GSR'     *
*    3  * 'GSR|EEG'            *
*    4  * 'EEG|EyeTracker'     *
*-------*----------------------*

I want to extract all unique sensor names, like so:

*------------*
* Sensor     *
*------------*
* EEG        *
* Eyetracker *
* GSR        *
*------------*

And then replace the single Sensor column with a table with a foreign key to that row:

* -----------*------------*
* Sensor     * Data.RowID *
* -----------*------------*
* EEG        * 1          *
* Eyetracker * 1          *
* GSR        * 1          *
* EyeTracker * 2          *
* GSR        * 2          *
* GSR        * 3          *
* EEG        * 3          *
* EEG        * 4          *
* EyeTracker * 4          *
* -----------*------------*

I've looked around, but this answer and this answer are specific to SQL Server, not SQLite. This answeris pure SQLite, but is not actually what I am looking for.

I think this may be a use-case for CTE's, but I don't know how I would go about using a CTE for this.

mooglinux
  • 815
  • 1
  • 11
  • 27
  • This is easy to do in pretty much any other language, e.g., Python. Why do you think that you need to do this in SQLite? – CL. Mar 30 '18 at 05:58
  • I am working with data imported from massive CSV files, so I prefer using SQL and letting the database figure out the details of processing it. I once processed a 3.5TB dataset in pure python, and am very keen on solutions that avoid repeating that experience. – mooglinux Mar 30 '18 at 07:24
  • 1
    What's wrong with `for rowid, names in db.execute("select rowid, sensor from data"): for name in names.split('|'): print(rowid, name)`? – CL. Mar 30 '18 at 09:23

0 Answers0