How to make a sql query with MAX dynamic

Question

I have a sql query like this. How to make it dynamic?

SELECT name,  
       MAX(CASE WHEN sub = 'maths'   then 'y' ELSE 'n' END) AS maths,
       MAX(CASE WHEN sub = 'science' then 'y' ELSE 'n' END) AS science,
       MAX(CASE WHEN sub = 'history' then 'y' ELSE 'n' END) AS history,
       MAX(CASE WHEN sub = 'computer'then 'y' ELSE 'n' END) AS computer,
       MAX(CASE WHEN sub = 'english' then 'y' ELSE 'n' END) AS english
FROM table t
GROUP BY name;

so that final result is like :

   name   maths science history computer    english
    a       y       y       y     y          y
    b       y       y       y     n          n
    c       y       n       y     y          n

Also how to select y or n as a column value? Will select work?

What is the sample data ? Question is not clear. Kindly clarify further. What is the use of MAX here ? — Madhur Bhaiya, Sep 01 '18 at 06:14
Possible duplicate of [How to display sql data in a specific format in html](https://stackoverflow.com/questions/52062705/how-to-display-sql-data-in-a-specific-format-in-html) — Madhur Bhaiya, Sep 01 '18 at 06:28
Yes, and people have already answered there. You should continue discussion there itself, in case you want further modifications — Madhur Bhaiya, Sep 01 '18 at 06:41
I am not clear what 'Also how to select y or n as a column value?' means - can you clarify please. — P.Salmon, Sep 01 '18 at 06:43
Please share the table structure of the table containing Y & N values. — Ankit Bajpai, Sep 01 '18 at 06:46
Consider handling issues of data display in application code — Strawberry, Sep 01 '18 at 08:33

score 1 · Accepted Answer · edited Sep 01 '18 at 08:16

1

select @cols := group_concat(distinct replace('MAX(CASE WHEN sub = ''[val]''   then ''y'' ELSE ''n'' END) AS `[val]`', '[val]', sub))
from (select distinct sub from t) t;

set @sql = concat('select name, ', @cols,
                  ' from t group by name'
                 );

prepare st from @sql;
execute st;
deallocate prepare st;

edited Sep 01 '18 at 08:16

P.Salmon

17,104
2
12
19

answered Sep 01 '18 at 07:38

Gordon Linoff

1,242,037
58
646
786

here instead of y and n values, how to display corresponding value from same row and different column? – qwww Sep 02 '18 at 07:02
@qwww . . . New questions should be asked as *questions* not comments. Please provide sample data and desired results as well. – Gordon Linoff Sep 02 '18 at 07:09

score 0 · Answer 2 · answered Sep 01 '18 at 06:50

set @sql = (
        select 
        concat('select name,',
        group_concat(
        concat('max(case when sub = ', 
        char(39),s.sub, char(39), ' then ',  char(39),'y',char(39),' else ', char(39),'n',char(39), ' end) as ',s.sub)
        ) 
        ,' from t group by name;')

from
(
select 'math' sub union select 'science' union select 'history'
) s
)
;

prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;

+------+------+---------+---------+
| name | math | science | history |
+------+------+---------+---------+
| a    | y    | y       | n       |
| b    | y    | y       | n       |
| c    | y    | n       | y       |
+------+------+---------+---------+
3 rows in set (0.00 sec)

Add or remove subjects from the union statements as required.

How to make a sql query with MAX dynamic

2 Answers2