how to dynamically convert rows into columns in mysql

Question

We are trying to convert rows into columns in order to display in row wise count.

Given a survey_details table, which look like

a busy cat

Given a vehicle_category table, which look like

a busy cat

how i can convert rows into columns in order to get count

the output which i am trying to get is

a busy cat

we have tried join on this table but unable to convert rows into column heading

select s.date,s.vehicle_id,v.category_name,count(s.vehicle_id) as vehicle_count from survey_details s inner join vehicle_category v on s.vehicle_id=v.vehicle_id group by s.vehicle_id;

Possible duplicate of [MySQL pivot row into dynamic number of columns](https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Raymond Nijland, Feb 24 '18 at 15:27
Seriously consider handling issues of data display in application code — Strawberry, Feb 24 '18 at 17:59

cdaiga · Answer 1 · 2018-02-24T15:57:44.143

Try this:

SELECT C.Period,
  SUM(C.Bike) Bike,
  SUM(C.Car) Car,
  SUM(C.Truck) Truck,
  SUM(C.Total) Total
FROM 
  (SELECT 
    DATE_FORMAT(A.`date`,'%d/%m/%Y') Period, 
    SUM(if(B.category_name='Bike',1,0)) Bike,
    SUM(if(B.category_name='Car',1,0)) Car,
    SUM(if(B.category_name='Truck',1,0)) Truck,
    COUNT(*) Total
   FROM survey_details A JOIN vehicle_category B
   ON A.vehicle_id=B.vehicle_id
   GROUP BY DATE_FORMAT(A.`date`,'%d/%m/%Y'), B.category_name) C
GROUP BY C.PERIOD;

See DEMO

how to dynamically convert rows into columns in mysql

1 Answers1