By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sal_table (sal CHAR(4));
INSERT INTO sal_table VALUES ('1val'), ('2val'), ('3val');
SELECT * FROM sal_table;
sal |
|---|
1val |
2val |
3val |
CREATE PROCEDURE get_data ()
BEGIN
SELECT CONCAT (
'select r.m_id, ',
GROUP_CONCAT(
CONCAT(
'count(distinct case when r.sal = ''',
sal,
''' then r.login end) as `',
sal,
'`'
)
),
' from read_log r ',
'inner join mes m on m.id = r.m_id ',
'where YEAR(m.send_date) = YEAR(curdate()) ',
'group by r.m_id'
)
INTO @sql
FROM sal_table;
SELECT @sql;
-- PREPARE stmt FROM @sql;
-- EXECUTE stmt;
-- DROP PREPARE stmt;
END
CALL get_data;
@sql |
|---|
select r.m_id, count(distinct case when r.sal = '1val' then r.login end) as `1val`,count(distinct case when r.sal = '2val' then r.login end) as `2val`,count(distinct case when r.sal = '3val' then r.login end) as `3val` from read_log r inner join mes m on m.id = r.m_id where YEAR(m.send_date) = YEAR(curdate()) group by r.m_id |