Geting the columns as rows from a single table in mysql

Question

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!

How many of those subjects are possible ? Are there only 2 ? — Abhik Chakraborty, Jan 22 '15 at 10:13
in subject assume it as a dynamic one i dont know subjects before writing query.it might be 2 subject or more than that it depends on department and i dont know subjects name it will vary every time — TULASI RAM, Jan 22 '15 at 10:25
i have clearly specified in the subject of this question. i need to write query in "MYSQL" — TULASI RAM, Jan 22 '15 at 10:28

Abhik Chakraborty · Answer 1 · 2015-01-22T11:08:47.040

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

DEMO

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;

DEMO

please assume that subject name are dynamic we dont know subject name before writing query thank you — TULASI RAM, Jan 22 '15 at 10:33

Geting the columns as rows from a single table in mysql

1 Answers1