CODEWITHSUNDEEP
mysql
jacob53
jacob53

Reputation: 35

MySQL CASE Statement Pivot issue

Please Suggest for converting table 1 to table2.. 

Actual Result:  
      User_id type   created_at
        1        1     2015-02-01
        1        2     2015-03-10
        1        3     2015-04-22 



Expected Result:

    user_id, type1_date, type2_date, type3_date

Query:

select user_id, 
       case when type=1 then created_at end as Type1,
       case when type=2 then created_at end as Type2,
       case when type=3 then created_at end as Type3 
from t1

Upvotes: 1

Views: 142

Answers (2)

Raging Bull
Raging Bull

Reputation: 18737

Problem in your query:

select user_id, 
       case when type=1 then created_at end as Type1,
       case when type=2 then created_at end as Type2,
       case when type=3 then created_at end as Type3 
from t1

You haven't used the aggregate function MAX(). So the query will not eliminate the fields with NULL values.

Solution:

Use MAX() and GROUP BY.

SELECT user_id,
       MAX(CASE WHEN type=1 THEN created_at END) AS type1_date,
       MAX(CASE WHEN type=2 THEN created_at END) AS type2_date,
       MAX(CASE WHEN type=3 THEN created_at END) AS type3_date
FROM t1
GROUP BY user_id

This is an alternative for pivot if the type column values are limitied:

Upvotes: 1

Saravana Kumar
Saravana Kumar

Reputation: 3729

Try this.

SELECT User_id,
     MAX(CASE WHEN type = 1 THEN created_at END) AS type1_date,
     MAX(CASE WHEN type = 2 THEN created_at END) AS type2_date,
     MAX(CASE WHEN type = 3 THEN created_at END) AS type3_date
FROM {your_table}
GROUP BY User_id

Upvotes: 0

Related Questions