I have one table like below here I need to get subject row as column and attendance as rows
sid name subject attendance 1 jhon sub1 1 2 toni sub1 0 3 danial sub1 1 4 jafer sub1 1 5 jhon sub2 1 6 toni sub2 1 7 danial sub2 1 8 jafer sub2 0 According to my requirements I need to get the output like the following table :
name sub1 sub2 jhon 1 1 toni 0 1 danial 1 1 jafer 1 0 I am new at writing queries in mysql. Thanks in advance for your help!
You can use the following technique to achieve this, note that it only works when you know the total variations in your case its just sub1 and sub2 , if there are more you need to add them in the query or generate a complete dynamic query
select name, max(case when subject='sub1' then attendance end ) as 'sub1', max(case when subject='sub2' then attendance end ) as 'sub2' from test group by name To make it dynamic its a bit complicated, google it about mysql dynamic pivoting and you will get some tutorial to understand how it works.
For your case you can have like below -
set @d_sql = null; select group_concat(distinct concat( 'max(case when subject = ''', subject, ''' then attendance else null end) as ', concat('`',subject, '`') ) ) into @d_sql from test; set @d_sql = concat('SELECT name,', @d_sql, ' from test group by name'); PREPARE stmt from @d_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 
