sqlite select data from column and insert into an other table as a row

Question

I have entries in (sqlite db) in table (patient) which contains (id, nom, pnom, ddn, date, bilan, ordonnance)

e.g. of the structure of 1 row:

<table border="1">
  <tr>
    <td>id</td><td>nom</td><td>pnom</td><td>ddn</td><td>date</td><td>bilan</td><td>ordonnance</td>
    </tr>
  <tr>
  <td>1</td><td>Someone</td><td>Someone</td><td>12/12/1992</td><td>12/01/2016</td><td>?12/01/2016!dat goes here?15/01/2016!dat goes here?nn/nn/nnnn!nnn goes here</td><td>?12/01/2016!dat goes here?15/01/2016!dat goes here?nn/nn/nnnn!nnn goes here</td>
  </tr>
</table>

I have created two tables "bilan" and "ordonnance", they both contain

id, pid, date, data

Is it possible, with sql, to:

select id, bilan from patient

then

insert into bilan (pid, date, data) Values (patient.id, '12/01/2016', 'dat goes here');
insert into bilan (pid, date, data) Values (patient.id, '15/01/2016', 'dat goes here');
insert into bilan (pid, date, data) Values (patient.id, 'nn/nn/nnnn', 'nnn goes here');

Same thing with "ordonnance"

Like [this](http://stackoverflow.com/a/3845931/3240704) however with sqllite3 there may be some changes — M O'Connell, Nov 09 '16 at 00:41

score 0 · Answer 1 · edited May 23 '17 at 11:53

I don't have SQLlite to test this, it's adapted from this answer

    UPDATE bilan
    SET
            pid =   (SELECT ordonnance.pid 
                                FROM ordonnance
                                WHERE ordonnance.id = bilan.id )
        ,   date =  (SELECT ordonnance.date 
                                FROM ordonnance
                                WHERE ordonnance.id = bilan.id )
        ,   data =  (SELECT ordonnance.data
                                FROM ordonnance
                                WHERE ordonnance.id = bilan.id )
    WHERE
        EXISTS (
            SELECT *
            FROM ordonnance
            WHERE ordonnance.id = bilan.id
        )

score 0 · Answer 2 · answered Nov 25 '16 at 01:24

The question was, probably, not clear. Here is, for example, an example of the column: "bilan" from the id: "17" in the table "patient"

?08/01/2014!0|1|0|1|0|0?04/03/2015!1|1|1|1|1|0?24/11/2016!0|0|0|0|1|1|0|0|0|0

the wanted result, in the table bilan, was those three rows:

id: 1 pid: 17 date: 08/01/2014 data: 0|1|0|1|0|0

id: 2 pid: 17 date: 04/03/2015 data: 1|1|1|1|1|0

id: 3 pid: 17 date: 24/11/2016 data: 0|0|0|0|1|1|0|0|0|0

my insert statement, in the application, was :

"UPDATE patient SET bilan = ifnull(bilan || '?" + today_  +"!" +$s+"', '?" + today_  +"!" +$s+"') WHERE ID="+oCurrent.id;

I made this solution:

Update patient set bilan=bilan|| '?' where bilan is not null;

then:

--steps from here to be repeated untill all bilan are null
insert into bilan ( pid, date, data )
SELECT id, 
substr(bilan,  instr(bilan, '?')+1 , instr(bilan, '!') -2),
substr(
    substr(bilan, instr(bilan, '!')+1),
    1,
    instr(  substr(bilan, instr(bilan, '!')+1), '?')-1
    )
FROM patient where bilan is not null;

--next
Update patient
set bilan = substr(
    substr(bilan,  instr(bilan, '?')+1),
    instr(substr(bilan,  instr(bilan, '?')+1), '?')
    )
where bilan is not null;
--next
Update patient set bilan = NULL where bilan ='?';
--end

sqlite select data from column and insert into an other table as a row

2 Answers2