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?

score 3 · Answer 1 · answered Feb 12 '12 at 12:37

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.

score 3 · Accepted Answer · edited Jan 18 '21 at 12:30

3

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

edited Jan 18 '21 at 12:30

Community

1
1

answered Feb 12 '12 at 12:39

Mahmoud Gamal

78,257
17
139
164

@FahimParkar, I think he his the same downvoter who downvotes our two posts in this thread: http://stackoverflow.com/questions/9157638/how-to-transform-vertical-fields-in-a-table-to-horizontal-result-by-sql, you also got a downvote for yours too: http://stackoverflow.com/questions/9157638/how-to-transform-vertical-fields-in-a-table-to-horizontal-result-by-sql/9157701#9157701, seems like someone gone mad with two of us, but he didn't downvotes yours in this one, however our two answers are the same in two questions. – Mahmoud Gamal Feb 12 '12 at 12:54
I didn't tried your this solution at that time... but it run smoothly... thanks I tested this time... might help me somewhere... – Fahim Parkar Feb 12 '12 at 12:58
@FahimParkar, Doesn't matter at all, the important thing is the OP got a helpful answer(I hope so), doesn't matter whether it gets vots up or down. – Mahmoud Gamal Feb 12 '12 at 13:01
@FahimParkar, you can try it as I pointed in the demo page: http://data.stackexchange.com/stackoverflow/query/61367 – Mahmoud Gamal Feb 12 '12 at 13:02
1

right :) OUR main AIM is solved, that is it.... Always happy to help.... and I would say your solution is perfect... NO joins and other tricky part... plain and simple... easy to digest (especially for newcomers) :) – Fahim Parkar Feb 12 '12 at 13:03
hmmmm.... are there any cons to using this method vs using the DB's pivot feature (if it has one)? I like this more b/c my favorite relational database (sqlite) doesn't have pivots. – user1481 Feb 12 '12 at 20:50
@user1481, no and it is also working for both MYSQL, and MSSQL and for MSSQL you can use `PIVOT` operator, but note that the problem with your query is that there is no aggregate column to aggregate your data with, so i used `max` as workaround, and the same if you used the `pivot` operator you will need to use an aggregate function. – Mahmoud Gamal Feb 12 '12 at 23:48

score 1 · Answer 3 · answered Feb 12 '12 at 12:37

1

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

answered Feb 12 '12 at 12:37

cynic

5,305
1
24
40

simple sql: how do I group into separate columns?

3 Answers3

DEMO

Linked