SQL query to get columns based on value

Question

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

Google "MySQL dynamic PIVOT".

PM 77-1
– PM 77-1

2015-06-08 05:13:20 +00:00
Commented Jun 8, 2015 at 5:13 — PM 77-1
– PM 77-1, Commented Jun 8, 2015 at 5:13

Giorgos Betsos · Accepted Answer · 2015-06-08 05:20:28Z

3

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

answered Jun 8, 2015 at 5:20

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

2 Comments

Petra Barus Over a year ago

Just curious, wha do you mean by dynamic SQL?

Giorgos Betsos Over a year ago

@PetraBarus Dynamic SQL is used when the query is programmatically built. In MySQL you can use prepared statements to execute such dynamically built queries.

Collectives™ on Stack Overflow

SQL query to get columns based on value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related