Mysql过程在行到列转换后只显示10列 [英] Mysql procedure showing only 10 columns after rows to column conversion

问题描述

For example:

Name  | 1stjan   | 2ndjan   | 3rdjan   | 4thjan
jenny | present  | absent   | present  | present
clark | absent   | present  | present  | present

Original tables - emp_details 

id | emp_id |   Name 
1  | 51     |  jenny
2  | 52     |  clark
Original tables - emp_attendance

 id | emp_id   | status       |    Date 
  1 |    51    |   present    |  2017/01/02    
  2 |    52    |   present    |  2017/01/02   

我的尝试：



我试过这个并且它工作正常但是在10列之后......它抛出了mysql语法的错误

What I have tried:

I tried this and its working fine but after 10 columns ...it throws an error of mysql syntax

DELIMITER $$
    DROP PROCEDURE IF EXISTS `attendance_reg`.`new2` $$
CREATE PROCEDURE new2 (IN ddl1 varchar(100))
BEGIN
SET @sql = NULL;
SET @vardomain := CONCAT(ddl1,'%');
    select GROUP_CONCAT(DISTINCT
               CONCAT(' Max(CASE WHEN DATE_FORMAT(b.time_in, ''%D %M'') = ''',
                       DATE_FORMAT(time_in, '%D %M'),
                       ''' THEN b.status END) AS ''',
                       DATE_FORMAT(time_in, '%D %M'), ''''
                     )
                  ORDER BY time_in)
                  INTO @sql
    from emp_attendance   where time_in like @vardomain;
    SET @sql = CONCAT('SELECT b.emp_id ,a.emp_name ,', @sql, ' from emp_details a  join emp_attendance b  on b.emp_id=a.emp_id  and b.time_in like ',@vardomain,' Group By a.emp_name order by a.emp_dept,a.emp_name');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END $$

DELIMITER ;

推荐答案

DROP PROCEDURE如果存在`attendance_reg`。 new2`

DROP PROCEDURE IF EXISTS `attendance_reg`.`new2`

CREATE PROCEDURE new2（IN ddl1 varchar（100））
BEGIN
SET @sql = NULL;
SET @vardomain：= CONCAT（ddl1，'％'）;
选择GROUP_CONCAT（DISTINCT
CONCAT（'Max（当时为DATE_FORMAT（b.time_in，''％D％M''）='''，
DATE_FORMAT（time_in，'％D） ％M'），
'''那么b.status END）AS'''，
DATE_FORMAT（time_in，'％D％M'），''''
）
ORDER BY time_in）
INTO @sql
来自emp_attendance，其中time_in喜欢@vardomain;
SET @sql = CONCAT（'SELECT b.emp_id，a.emp_name，'，@ sql，'from emp_details a join emp_attendance b on b.emp_id = a.emp_id and b.time_in like'，@ vardomain， '按a.emp命令分组a.emp_dept，a.emp_name'）;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

CREATE PROCEDURE new2 (IN ddl1 varchar(100)) BEGIN SET @sql = NULL; SET @vardomain := CONCAT(ddl1,'%'); select GROUP_CONCAT(DISTINCT CONCAT(' Max(CASE WHEN DATE_FORMAT(b.time_in, ''%D %M'') = ''', DATE_FORMAT(time_in, '%D %M'), ''' THEN b.status END) AS ''', DATE_FORMAT(time_in, '%D %M'), '''' ) ORDER BY time_in) INTO @sql from emp_attendance where time_in like @vardomain; SET @sql = CONCAT('SELECT b.emp_id ,a.emp_name ,', @sql, ' from emp_details a join emp_attendance b on b.emp_id=a.emp_id and b.time_in like ',@vardomain,' Group By a.emp_name order by a.emp_dept,a.emp_name'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END

DELIMITER;

DELIMITER ;

这篇关于Mysql过程在行到列转换后只显示10列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！