Sqlite insert both even and odd rows in one expression

Question

I am using sqlite3 and I have a sqlite table which has somewhat duplicated/overlapping columns. To illustrate:

       No    Col1  Col2  Col3  Col4
row1   1       1     1     2     2
row2   2       1     1     3     3
row3   3       2     2     4     4
row4   4       2     2     5     5

Col1 and Col2 stores the same information, however, Col3 and Col4 has different information.

I want to condense the rows into one row like this:

        No    Col1  Col2  Col3  Col4  Col3.2  Col4.2
row1     1     1     1     2     2     3     3
row3     3     2     2     4     4     5     5

I have created a new table with the columns, and was able to select the odd rows.

INSERT INTO [Table] ( No, Col1, Col2, Col3, Col4                            
                        )
                        SELECT No, Col1, Col2, Col3, Col4  
                          FROM [Table]
                         WHERE ([No] % 2) = 1
                         ORDER BY [No];

The result table would be something like:

      No  Col1  Col2  Col3  Col4 Col3.2 Col4.2
row1  1     1     1    2     2    null   null
row3  3     2     2    4     4    null   null

Now I am not sure how to insert the even values into the new table. Using similar expressions only insert more rows. Is it possible to do this INSERT INTO expression in one sentence? Or how do I update the new table?

Answer 1

Just join the table with itself based on the following condition. It'll even work if the No column has gaps:

SELECT o.No, o.Col1, o.Col2, o.Col3, o.Col4, e.Col3, e.Col4
FROM t AS o
INNER JOIN t AS e ON  o.Col1 = e.Col1
                  AND o.Col2 = e.Col2
                  AND o.No < e.No

Answer 2

Use pivoting logic with aggregation:

SELECT
    MIN(No) AS No,
    MAX(CASE WHEN No % 2 = 1 THEN Col1 END) AS Col1,
    MAX(CASE WHEN No % 2 = 1 THEN Col2 END) AS Col2,
    MAX(CASE WHEN No % 2 = 1 THEN Col3 END) AS Col3,
    MAX(CASE WHEN No % 2 = 1 THEN Col4 END) AS Col4,
    MAX(CASE WHEN No % 2 = 0 THEN Col1 END) AS Col1_2,
    MAX(CASE WHEN No % 2 = 0 THEN Col2 END) AS Col2_2,
    MAX(CASE WHEN No % 2 = 0 THEN Col3 END) AS Col3_2,
    MAX(CASE WHEN No % 2 = 0 THEN Col4 END) AS Col4_2
FROM yourTable
GROUP BY
    (No-1) / 2;

Demo

Answer 3

Another approach, using window functions added in sqlite 3.25:

CREATE TABLE table2(no INTEGER PRIMARY KEY, col1, col2, col3, col4, "col3.2", "col4.2");
INSERT INTO table2
SELECT *
FROM (SELECT no, col1, col2, col3, col4, lead(col3) OVER win, lead(col4) OVER win
      FROM table1
      WINDOW win AS (ORDER BY no))
WHERE no % 2 = 1;

which gives

SELECT * FROM table2;
no          col1        col2        col3        col4        col3.2      col4.2    
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           1           1           2           2           3           3         
3           2           2           4           4           5           5