CODEWITHSUNDEEP
sqlpostgresql
dan sawyer
dan sawyer

Reputation: 193

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

How to select multiple entries in a single query.

The db table structure represents an excel like spreadsheet. Each observation is a workbook. 

Work book 1  
1     2  
3     4  

Work book 2  
5    6  
7    8

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

observation integer 
row_number integer
col_number integer
value integer

The database then looks like 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 is how to create a single query to: 

select observation, value as value1 from database where row_number = 1 and col_number = 2,
select value as value2 from database where row_number = 2 and col_number = 1;

to create:

observation value1  value2  
1             2      3  
2             6      7

I have tried joins and subqueries.

Upvotes: 0

Views: 162

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

The basic answer is with a self-join. The table name 'database' is pretty objectionable: I'm going to call it 'spreadsheet'.

 SELECT r1.observation, r1.value AS value1, r2.value AS value2
   FROM spreadsheet AS r1
   JOIN spreadsheet AS r2
     ON r1.observation = r2.observation
  WHERE r1.row_number = 1
    AND r1.col_number = 2
    AND r2.row_number = 2
    AND r2.col_number = 1

There are plenty of other ways of writing the same query. One of them is:

 SELECT r1.observation, r1.value1, r2.value2
   FROM (SELECT observation, value AS value1
           FROM spreadsheet
          WHERE r1.row_number = 1
            AND r1.col_number = 2
        ) AS r1
   JOIN (SELECT observation, value AS value2
           FROM spreadsheet
          WHERE r2.row_number = 2
            AND r2.col_number = 1
        ) AS r2
     ON r1.observation = r2.observation

Upvotes: 2

shawnt00
shawnt00

Reputation: 17915

Jonathan has a great answer too but you might find this approach is better if you're going to need to grab more than two values from the observation. I think his joins should be fast but I have no idea how big your tables are.

Mine requires you to specify the (row, column) pairs in multiple places which is a small disadvantage.

select
    observation,
    min(case when row_number = 1 and column_number = 2 then value end) as value1,
    min(case when row_number = 2 and column_number = 1 then value end) as value2
from T
where (row_number = 1 and column_number = 2) or (row_number = 2 and column_number = 1)
group by observation

Upvotes: 1

Related Questions