0

I have one table as job_status which is having columns as date, status

here is the query which i prepared

SELECT DATE_FORMAT(date,'%D %M %Y') DATE, status STATUS, 
COUNT(*) COUNT
FROM job_status
where date BETWEEN '2022-10-13 00:00:00' AND '2022-10-18 23:59:59' 
GROUP BY DATE_FORMAT(created_on,'%D %M %Y'), status

But the current results shows as

DATE    STATUS      COUNT
14-Oct  Pending     5
14-Oct  Completed   5   
14-Oct  HOLD        6   
15-Oct  Pending     1   
15-Oct  Completed   2   
15-Oct  HOLD        3   
16-Oct  Pending     4   
16-Oct  Completed   5   
16-Oct  HOLD        6

Status can be dynamic or any other, hence i have not kept in where condition.

But i want to show the results as

STATUS      14-OCT  15-OCT  16-OCT
Pending     5       1       4
Completed   5       2       5
HOLD        5       3       6
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user1251973
  • 341
  • 3
  • 7
  • 16
  • 1
    This is "PIVOT". Search for accepted solutions. – Akina Oct 18 '22 at 13:31
  • *`DATE_FORMAT(date,'%D %M %Y')`* cannot produce `'14-Oct'`.. – Akina Oct 18 '22 at 13:32
  • I voted to make this question a duplicate of a past question tagged mysql and pivot-table. You may find other solutions with the same tags: https://stackoverflow.com/questions/tagged/mysql+pivot-table – Bill Karwin Oct 18 '22 at 13:44
  • There is no such thing as "dynamic columns" in an SQL query. You must list the columns explicitly at the time the query is parsed. There's no way for the query to append more columns to the select-list during execution. – Bill Karwin Oct 18 '22 at 13:45
  • A stored proc to do the task: http://mysql.rjweb.org/doc.php/pivot – Rick James Oct 18 '22 at 18:31

0 Answers0