0

I have created the following working query that counts all tickets by weeks, but I need to convert it to a two-row report that moves the week-beginning dates to columns?

Here is my working query:

SELECT td_type, FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7)) AS week_beginning,
   DATE_ADD( DATE(ttDate), INTERVAL (8 - DAYOFWEEK(ttDate)) DAY) week_ending,
   count(*)
FROM v_all_tickets
WHERE extract(year from ttDate) = '2018' AND ttCategory IN ('TT')
GROUP BY td_type, FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7))
ORDER BY FROM_DAYS(TO_DAYS(ttDate) -MOD(TO_DAYS(ttDate) -2, 7)) DESC, td_type

Here are the results: enter image description here

How do I transform my query above to display the data like this? enter image description here

jlig
  • 241
  • 2
  • 12
  • Not clear buddy. what is the table format you need to create??? – Rumesh Jun 25 '18 at 16:10
  • No one can help you if no one can understand you. Learn how to ask question. https://stackoverflow.com/help/how-to-ask – Eric Jun 25 '18 at 16:11
  • This transformation is called pivoting and has been asked and answered here on SO sooo many times. The linked duplicate topic demonstrates how to perform dynamic pivoting (number of columns is not known in advance) in MySQL. However, pls note that it may be more efficient to perform these transformations in the application code as opposed to MySQL. – Shadow Jun 25 '18 at 16:24
  • sorry about that.. I've updated the post. – jlig Jun 25 '18 at 16:34
  • ok, I'll repost as a new question. – jlig Jun 26 '18 at 15:03

0 Answers0