Create PIVOT using mysql database and C#

Abdu Abdul

Good day everyone.

Please i have a small problem and easy for the genuis in this group. I want to PIVOT MYSQL data base on number of days in a month choosen

sample data

attendance	date
primary school	1-1-2020
primary school	1-1-2020
secondary school	1-1-2020
secondary school	1-1-2020
secondary school	2-1-2020
primary school	2-1-2020
primary school	3-1-2020

SQL SERVER has a PIVOT statement which is not available on MYSQL. my problem is i want to group the data base on the current month i chose, i dont want to use CASE statement because i cant i achieve what i want

my expected outcome, let say i chose january as a month, i want the outcome to look like this

attendance	1-1-2020	2-1-2020	3-1-2020	4-1-2020	5-1-2020	6-1-2020	7-1-2020
primary school	2	1	1
secondary school	2	1

i want the outcome to give me complete month days irrespective of choosen month, i can get the sum of data but to get the complete month days is what i need help from. if any one has a better alternative can suggest to me please.

thank you all.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Can you please let me know the reason why you do not want to use CASE expression if it will serve your purpose.

Just curious.
Muhammad Imran Ansari4y ago
Try the following query for result. For date range you can generate a string for respective date.

select *
from
(
select attendance, [date]
from AttendanceRecord
) src
pivot
(
COUNT([date])
for date in ([2020-01-01], [2020-02-01], [2020-03-01], [2020-04-01])
) piv
Abdu Abdul4y ago
Thank you for your answer, is really amazing to reply back immediately, like i said i dont want to use case fixed date days, i want it in such a way the moment you select jan..... then it gives the whole of january days 1-31 thank you very much
Sachin Singh4y ago
1. attendence,
2. COUNT(IF(date= '1-1-2020', 1, NULL)) AS 1-1-2020,
3. COUNT(IF(date= '2-1-2020', 1, NULL)) AS 2-1-2020,
4. COUNT(IF(date= '3-1-2020', 1, NULL)) AS `3-1-2020`,
5. COUNT(IF(date= '4-1-2020', 1, NULL)) AS `4-1-2020`,
6. COUNT(IF(date= '5-1-2020', 1, NULL)) AS `5-1-2020`,
7. COUNT(IF(date= '6-1-2020, 1', NULL)) AS `6-1-2020`,
8. COUNT(IF(date= '7-1-2020', 1, NULL)) AS `7-1-2020`
9. FROM
10. table p
11. WHERE
12. p.date BETWEEN '1-1-2020'AND '7-1-2020'
13. GROUP BY
14. attendence;