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

Question

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.

Please restate your question explaining in words what you need to accomplish and what you have specifically done which is not working. This makes no sense at all. — steve klein
– steve klein, Commented Apr 26, 2015 at 2:56
The database table is made up of 4 columns. It represents observations recorded in excel. Each observation is made up of multiple work sheets. The database column 1 represents the observation. Database column 2 represents the row. Database column 3 represents column. Data base column 4 represents the value. The question is: how to a table with three columns. the observation, row 1-col 2 and row 3-col 1 — dan sawyer
– dan sawyer, Commented Apr 26, 2015 at 3:21

Jonathan Leffler · Accepted Answer · 2015-04-26 04:01:27Z

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

shawnt00 · Accepted Answer · 2015-04-26 04:01:01Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related