Thursday, September 11, 2014

CSV format from MySQL View



With the latest Technologies and Tools available its very easy to export data select from database to CSV format.

But most of them are not able to to configure as you want.

Here is a sample for MySql for Student Data from client_marks_view View


set @given_from:='2014-01-01';
set @given_to:='2014-06-01';

SELECT CONCAT(' Students Marks Summary for the period ',`given_from`(), ' - ',`given_to`()) AS `Students Marks Summary`
UNION ALL
SELECT '' AS ``
UNION ALL
SELECT CONCAT('"Student Name','",','"Total Attendance','",','"Total Marks','"') AS `CONCAT('"Student','",','"Resource Name','",','"Total Attendance','",','"Total Marks')`
UNION ALL
(SELECT CONCAT('"',ifnull(`a`.`studnet_name`,''),'","',ifnull(`a`.`total_attendance`,' '),'","$',round(ifnull(`a`.`total_marks`,0),2),'",') AS `CONCAT('"',
IFNULL(a.student_name, ""),
'","',
IFNULL(a.total_attendance," "),
'","',
IFNULL(a.total_marks, "")
ROUN` from `student_marks_view` `a`) ;


How to create a direct view from that

CREATE OR REPLACE VIEW students_view
AS

   SELECT CONCAT(' Student Marks Summary for the period ',`given_from`(), ' - ',`given_to`()), ',,,,,,'
     UNION ALL
   SELECT "", "" FROM DUAL
     UNION ALL
   SELECT CONCAT('"Student Name','",','"Total Attendance','",','"Total Marks'),'",,,,,,'
     UNION ALL
 
   (SELECT CONCAT('"',
IFNULL(a.student_name, ""),
'","',
IFNULL(a.total_attendance," "),
'","$',
ROUND(IFNULL(a.total_marks, 0),2),
'",'), ',,,,,,' 

FROM student_marks_view  AS a )