CODEWITHSUNDEEP
mysqlsql
Petra Barus
Petra Barus

Reputation: 4013

SQL query to get columns based on value

I have a table like below

user_id  | month,     | value
---------+------------+--------
1        | 2013-02-01 | 1
1        | 2013-01-01 | 0
1        | 2013-03-01 | 5
2        | 2013-02-01 | 1

Supposedly a user_id can not have same month more than one.

Let's say I can put the months I want to query in the query statement. like below

SELECT user_id, (bla bla bla) AS '2013-03-01', (bla bla bla) AS '2013-02-01'

How do I get a result like below, with minimum number of queries and post-processing (e.g. using python or php)?

user_id  | 2013-03-01 | 2013-02-01 | 2013-01-01 
---------+------------+------------+------------
1        | 5          | 1          | 0
2        | NULL       | 1          | NULL

Upvotes: 2

Views: 54

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use conditional aggregates to get the required result set:

SELECT user_id,
       MAX(CASE WHEN month = '2013-03-01' THEN value END) AS '2013-03-01',
       MAX(CASE WHEN month = '2013-02-01' THEN value END) AS '2013-02-01',
       MAX(CASE WHEN month = '2013-01-01' THEN value END) AS '2013-01-01'
FROM mytable
GROUP BY user_id

This works as long there is a predefined set of month values. Otherwise you have to use dynamic SQL.

Fiddle Demo here

Upvotes: 3

Related Questions