Say I keep stocks prices in a 3 column table like this:
create table stocks(
ticker text,
day int,
price int
);
insert into stocks values ('aapl', 1, 100);
insert into stocks values ('aapl', 2, 104);
insert into stocks values ('aapl', 3, 98);
insert into stocks values ('aapl', 4, 99);
insert into stocks values ('goog', 1, 401);
insert into stocks values ('goog', 2, 390);
insert into stocks values ('goog', 3, 234);
And I want results that look like:
day aapl goog
1 100 401
2 104 390
3 98 234
4 99 null
Do I really need to select twice, once for each ticker, and then outer join the results?
Like this:
Select day,
MAX(case WHEN ticker = 'aapl' then price end) as 'aapl',
MAX(case WHEN ticker = 'goog' then price end) as 'goog'
From stocks
group by day
DEMO
Regardless of the database you are using, the concept of what you are trying to achieve is called "Pivot Table".
Here's an example for mysql:
http://en.wikibooks.org/wiki/MySQL/Pivot_table
Some databases have builtin features for that, see the links below.
SQLServer:
http://msdn.microsoft.com/de-de/library/ms177410.aspx
Oracle:
http://www.dba-oracle.com/t_pivot_examples.htm
You can always create a pivot by hand. Just select all the aggregations in a result set and then select from that result set.
Note, in your case, you can put all the names into one column using concat (i think that's group_concat in mysql), since you cannot know how many names are related to a ticker.
Yes you do, unless your DB has SQL extensions for pivoting. Here's how you do it in Microsoft SQL Server.
