Query to select value from another table as a column-CodePudding

I have a query to report some data :

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

r.sal value in count(distinct case when r.sal = '1val' then r.login end) as 1val only changes. Finally it shows results in each column for every r.sal. There are currently over 80 diffrent r.sal and its growing.

My question is: It is possible to take value for r.sal from another table? Like new table sal with this 1val, 2val, 3val, 4val, 5val etc...? Maybe loop or something like that: count(distinct case when r.sal = (select val from sal) then r.login end) (I know its wrong but maybe it will illustrate it better)

count(distinct case... is great to show data for each r.sal value in the other column but maybe is another way to achieve that...

CodePudding user response：

CREATE TABLE sal_table (sal CHAR(4));
INSERT INTO sal_table VALUES ('1val'), ('2val'), ... ;

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;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
END

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=af55c52aca3280410fba1f3a453aab09

PS. Recommended edition: WHERE m.send_date >= DATE_FORMAT(CURRENT_DATE, '%Y-01-01') AND m.send_date < DATE_FORMAT(CURRENT_DATE INTERVAL 1 YEAR, '%Y-01-01'). Such condition is sargable rather than your one.