sql - How to select data from several rows in a single table in one result row? -

how select multiple entries in single query.

the db table structure represents excel spreadsheet. each observation workbook.

work book 1   1     2   3     4    work book 2   5    6   7    8   

with each db row representing single cell , observations in separate workbooks. table definition is:

observation integer  row_number integer col_number integer value integer 

the database looks this:

observation row_number  col_number  value       1        1             1        1       1        1             2        2       1        2             1        3       1        2             2        4       2        1             1        5       2        1             2        6       2        2             1        7       2        2             2        8   

the question how create single query to:

select observation, value value1 database row_number = 1 , col_number = 2, select value value2 database row_number = 2 , col_number = 1; 

to create:

observation value1  value2   1             2      3   2             6      7  

i have tried joins , subqueries.

the basic answer self-join. table name 'database' pretty objectionable: i'm going call 'spreadsheet'.

 select r1.observation, r1.value value1, r2.value value2    spreadsheet r1    join spreadsheet r2      on r1.observation = r2.observation   r1.row_number = 1     , r1.col_number = 2     , r2.row_number = 2     , r2.col_number = 1 

there plenty of other ways of writing same query. 1 of them is:

 select r1.observation, r1.value1, r2.value2    (select observation, value value1            spreadsheet           r1.row_number = 1             , r1.col_number = 2         ) r1    join (select observation, value value2            spreadsheet           r2.row_number = 2             , r2.col_number = 1         ) r2      on r1.observation = r2.observation