sqlite – combine same field in 2 rows into 2 columns based on field value

I have a sqlite table with data similar to the following:

 id | flag
----+-----
  1 |  0
  2 |  0
  3 |  1
  4 |  0
  5 |  1
  6 |  0
  7  | 1

I’d like to get results that look like the following:

id1 | id2
----+-----
  1 | null
  2 | null
  3 |   5
  4 | null
  6 | null
  7 | null

To explain, I’m trying to “combine” the id’s of each “pair” of records (in order) where “flag == 1”. Where the record has “flag == 0”, id2 should just be NULL. Also notice, since I have a odd number of records where “flag == 1” (in this example), the final one (id = 7) doesn’t have an additional record to pair with, so it too has an id2 field of NULL.

Is this possible via pure SQL (sqlite3 in this case)? I can obviously post-process the results in the host application, but was hoping to not have to.

*** EDIT ***

Adding the requested MRE…

CREATE TABLE test (
    id INTEGER NOT NULL PRIMARY KEY,
    flag INTEGER NOT NULL );
 
INSERT INTO test(flag) VALUES(0),(0),(1),(0),(1),(0),(1);

Answer

You can do it with window functions MIN() and ROW_NUMBER():

SELECT id id1, CASE WHEN flag THEN next END id2 
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY flag ORDER BY id) rn,
         MIN(CASE WHEN flag THEN id END) OVER (ORDER BY id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) next
  FROM test
)
WHERE flag = 0 OR rn % 2 = 1
ORDER BY id1

See the demo.
Results:

id1	id2
1	null
2	null
3	5
4	null
6	null
7	null

Advertisement

Answer