1

i have a data in table like this: 

=================================
  name  |  study  |  avg_scores
=================================
  alfa     c#         75
  beta     c#         70
  alfa     php        85
  beta     php        90

and i want the result like this :

===========================
 name |   c#    |   php   
===========================
 alfa     75        85     
 beta     70        90

so how to make a query in mysql to get those results? Thanks :)

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
user3747046
  • 39
  • 5
  • possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) – PM 77-1 Jul 12 '14 at 17:20
  • Do you know the number of study subjects at design time? – PM 77-1 Jul 12 '14 at 17:21

2 Answers2

1

Try this:

select first.name,
  max(if(first.study = 'c#', first.avg_scores, NULL)) as 'c#',
  max(if(first.study = 'php', first.avg_scores, NULL)) as php
from `table` first 
join `table` second
  on first.name = second.name
group by first.name

Fiddle: http://sqlfiddle.com/#!2/3c61c/1

For dynamic ones:

set @sql = NULL;
select
  group_concat(distinct
    concat(
     'max(if(first.study = ''',
      study, ''', first.avg_scores, NULL)) as ',
      study
    )
  ) into @sql
from `table`;

SET @sql = concat('select first.name,
                  ', @sql, ' 
                   from `table` first
                   join `table` second
                   on first.name = second.name
                   group by first.name');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

Fiddle: http://sqlfiddle.com/#!2/49c560/2

Burak
  • 5,252
  • 3
  • 22
  • 30
  • it's works . then what if type of study is not limited. for example: there will be a study => 'VB6', 'xml', 'json' and others – user3747046 Jul 12 '14 at 17:58
  • error: #1243 - Unknown prepared statement handler (stmt) given to EXECUTE – user3747046 Jul 14 '14 at 02:44
  • have you escaped c# or either removed `#`? also you need to reaarange the query if your column name is different, check the fiddle.. – Burak Jul 14 '14 at 06:53
  • sorry, my error because the data in the column study two syllables. for example: "junior php", "junior c". and i have error => "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c,max(if(first.study = 'junior php', first.avg_scores, NULL)) as junior php ' at line 2: prepare stmt from @sql" – user3747046 Jul 15 '14 at 05:18
  • so then have you fixed it? – Burak Jul 15 '14 at 12:19
0

Your query will be:

select n.name, c.avg_scores as C_AVG, p.avg_scores as PHP_AVG from `table` n join `table` c on n.name=c.name and c.study="c#" join `table` p on n.name=p.name and p.study="php" group by n.name

UPDATE: If the type of study doesn't matter, try this:

select name, study, avg_scores from `table` group by name, study

If will not return by column, but it's working well.

g.carvalho97
  • 332
  • 1
  • 9