CODEWITHSUNDEEP
mysql
Dennis Duijsters
Dennis Duijsters

Reputation: 11

Create columns based on 1 Value Column and 1 Condition Column

HELP!

I do not seem to be able to solve this simple MYSQL problem.

I have data in this format in a source table:

Date_type        Date              Activity_id
Preferred        13/02/16 07:30    1
Planned          13/02/16 08:30    1
Actual           13/02/16 09:30    1
Preferred        14/02/16 07:30    2
Planned          14/02/16 08:30    2
Actual           14/02/16 09:30    2

And I need it like this in a report:

Activity_id  Preferred_Date    Planned_Date     Actual_DateDate              
1            13/02/16 07:30    13/02/16 08:30   13/02/16 09:30
2            14/02/16 07:30    14/02/16 08:30   14/02/16 09:30

I've been googling for hours now and I've tried all variations on Select if/case I know. I do not see how I can convert this. Thanks for your help, guys, really.

Upvotes: 1

Views: 37

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562330

This is a good way to do a pivot in MySQL:

SELECT Activity,
  MAX(CASE `Type` WHEN 'Preferred' THEN `Value` END) AS Preferred_date,
  MAX(CASE `Type` WHEN 'Planned' THEN `Value` END) AS Planned_date,
  MAX(CASE `Type` WHEN 'Actual' THEN `Value` END) AS Actual_date
FROM MyTable
GROUP BY Activity;

I delimited Type and Value in back-ticks because they are MySQL Reserved Words. Usually I prefer to avoid using reserved words as column names.

Re your comments:

A pivot query is when your data is in rows and you want to format specific values into columns, usually depending on the value in another field. So for example, in your case, you have rows of data for different Type values, and you want to map the timestamp values into columns labeled by the respective Type. This is a pivot of rows into columns.

MAX() is more or less arbitrary, I could have used MIN() as well. It just means we're applying an aggregating function to a set of rows, grouped by each distinct value of Activity.

CASE is an expression in SQL. The syntax is fairly self-explanatory, but you need to know that the whole CASE expression returns NULL if no case matches the data. And MAX()/MIN() ignore NULLs. So you should just get one distinct non-NULL Value for each Type in each group of rows.

Upvotes: 2

Related Questions