simple sql: how do I group into separate columns?

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

Yes you do, unless your DB has SQL extensions for pivoting. Here's how you do it in Microsoft SQL Server.