create columns and rows mysql

Question

I have table data such as this

index  user  date   rank
  11     a    1Mar    23
  12     b    1Mar    16
  13     a    2Mar    24
  14     b    2Mar    18

What I would like to achieve via a query is this:

    1Mar   2Mar
a   23     24
b   16     18

I don't know if this can be done via a single statement at the command line or if this will have to be done via a form and some scripting. Doing through scripting I can do, but can't see how to do in a single statement.

It can be done in a single statement.

ForguesR
– ForguesR

2014-10-20 03:41:38 +00:00
Commented Oct 20, 2014 at 3:41 — ForguesR
– ForguesR, Commented Oct 20, 2014 at 3:41

radar · Accepted Answer · 2014-10-20 03:44:47Z

1

you can do pivot like below, if you know all possible values for date or you need to use dynamic sql.

SELECT user,
       MAX( CASE WHEN date ='1Mar' THEN rank else NULL end) AS '1Mar',
       MAX( CASE WHEN date ='2Mar' THEN rank else NULL end) AS '2Mar'
FROM Table1
GROUP BY user

answered Oct 20, 2014 at 3:44

radar

13.4k2 gold badges27 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

create columns and rows mysql

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