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
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.