CODEWITHSUNDEEP
mysql
ShirleyKuanSF
ShirleyKuanSF

Reputation: 83

Converting MYSQL row value into column names

I have tables like this:

job_status_air

status_id  | status
-----------+------------
1          | AIRPORT IN
2          | AIRPORT OUT

job_status_air_pkg

id  | status_id  | package_no
----+------------+------------
1   | 1          | pkg1
2   | 1          | pkg2
3   | 1          | pkg3
4   | 2          | pkg1

I am using the below query:

select package_no , 
       (case when job_status_air.status = "AIRPORT IN" then job_status_air_pkg.id end) AIRPORTIN, 
       (case when job_status_air.status = "AIRPORT OUT" then job_status_air_pkg.id else 0 end) AIRPORTOUT 
FROM job_status_air_pkg 
LEFT JOIN job_status_air ON (job_status_air.status_id = job_status_air_pkg.status_id) 
WHERE job_status_air.mawb = 'awb1' or hawb = 'awb1'

I can't achieve the below result:

package_no | AIRPORT IN | AIRPORT OUT
-----------+------------+-------------
 pkg1      |    1       |  4
 pkg2      |    2       |
 pkg3      |    3       |

Can anybody help me please?

Upvotes: 1

Views: 45

Answers (1)

Fahmi
Fahmi

Reputation: 37473

you need to apply aggregation and group by 

select package_no , 
       max(case when job_status_air.status = "AIRPORT IN" then job_status_air_pkg.id end) AIRPORTIN, 
       max(case when job_status_air.status = "AIRPORT OUT" then job_status_air_pkg.id else 0 end) AIRPORTOUT 
FROM job_status_air_pkg 
LEFT JOIN job_status_air ON (job_status_air.status_id = job_status_air_pkg.status_id) 
WHERE job_status_air.mawb = 'awb1' or hawb = 'awb1' 
group by package_no

Upvotes: 2

Related Questions