将重复行转换为新列MySql

Question

Basically I have a MySql table

--------    -----------   --------
 cv_id       all_stages    amount
--------    -----------   --------
  12           S1           5
  13           S4           2
  13           S2           1  
  14           S1           3
  14           S3           2

I have to write a query so that, based oncv_id, I need to have a single entry. And I need to generate few more columns.

What I want is

--------    ---------  --------  --------  --------   -------
 cv_id       stage1     stage2    stage3    stage4     total
--------    ---------  --------  --------  --------   -------
  12           S1                                       5
-------------------------------------------------------------
  13                      S2                  S4        3
-------------------------------------------------------------
  14           S1                   S3                  5
-------------------------------------------------------------

How should I arrange all stages to the respective stage columns based on cv_id and totaling the amount column?

P.S - I need to execute query to generate new columns

Answer 1

This is not a good practice to generate columns dynamically. Columns represents common properties for a model, and you will end up with a table that has 100 columns and only one column is used per row. What you are trying to do, can be achieved with another table that links cv_id with stages.

For example

table cv_stage
Columns: cv_id, stage_name

You can then always query cv_id's that have certain stages or any other information you may need.