SQLite join selection from the same table as two columns

Question

I have one table 'positions' with columns:

id | session_id | keyword_id | position

and some rows in it:

• 10 rows with session_id = 1
• and 10 with session_id = 2.

As a result of the query I need a table like this:

id | keyword_id | position1 | position2

where 'position1' is a column with values that had session_id = 1 and 'position2' is a column with values that had session_id = 2.

The result set should contain 10 records.

Sorry for my bad English.

Data examle:

id  | session_id | keyword_id | position
1   | 1          | 1          | 2
2   | 1          | 2          | 3
3   | 1          | 3          | 0
4   | 1          | 4          | 18
5   | 2          | 5          | 9
6   | 2          | 1          | 0
7   | 2          | 2          | 14
8   | 2          | 3          | 2
9   | 2          | 4          | 8
10  | 2          | 5          | 19

Answer 1

Assuming that you wish to combine positions with the same id, from the two sessions, then the following query should to the trick:

SELECT T1.keyword_id
     , T1.position as Position1
     , T2.position as Position2
  FROM positions T1
       INNER JOIN positions T2
               ON T1.keyword_id = T2.keyword_id  -- this will match positions by [keyword_id]
              AND T1.session_id = 1 
              AND T2.session_id = 2