SQL queries basics - returning multiple data over different dates

Question

Having trouble with basic sql query, I need to summarize a lot of information and the query i'm using is:

SELECT date, id, actions
FROM accounts 
WHERE (date between '2013-07-01' and '2013-9-30') AND (id in (---ids---));

Right now I'm getting

date 1    id1   actions
date 2    id1   actions
date 3    id1   actions
date 4    id1   actions
date 5    id1   actions
date 6    id1   actions

date 1    id2   actions
date 2    id2   actions
date 3    id2   actions
date 4    id2   actions
date 5    id2   actions
date 6    id2   actions

what i want is

date 1    id1   actions  id2  actions
date 2    id1   actions  id2  actions
date 3    id1   actions  id2  actions
date 4    id1   actions  id2  actions
date 5    id1   actions  id2  actions
date 6    id1   actions  id2  actions

What's the easiest way of doing this?

Are you using a specific SQL server, or asking about SQL in general? — Rogue
– Rogue, Commented Oct 28, 2013 at 20:00
How do you relate id1 to id2? Or, how is id1 and id2 related to the dates? — Linger
– Linger, Commented Oct 28, 2013 at 20:01
You can use a PIVOT to put them into something similar but with id1 and id2 as the column names. — zimdanen
– zimdanen, Commented Oct 28, 2013 at 20:01
Yeah didn't think about using a pivot table to do it, thanks! — neil4real
– neil4real, Commented Oct 28, 2013 at 20:13

Gordon Linoff · Accepted Answer · 2013-10-28 20:02:14Z

2

Assuming that there is only one id1 and id2 on a given date, then this is a pivot query. There is special syntax in some databases for a pivot, but the following works in general:

SELECT date,
       max(case when id = 'id1' then id end) as id1,
       max(case when id = 'id1' then actions end) as id1_actions,
       max(case when id = 'id2' then id end) as id2,
       max(case when id = 'id2' then actions end) as id2_actions
FROM accounts 
WHERE (date between '2013-07-01' and '2013-9-30') AND (id in (---ids---))
group by date
order by date;

Note that date is a keyword in some databases, so you would need to quote it in some way (such as using double quotes, back quotes, or square brackets).

answered Oct 28, 2013 at 20:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL queries basics - returning multiple data over different dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related