c# - customize query result -

i have 3 tables

1. tblindividual
2. tblindividualspeciality
3. tblspeciality

one individual have multiple speciality, when write query many rows depends number of speciality individual. need record in single row , each speciality have single column.

this table structure

my query

select firstname,lastname,specialtyname tblindividual ind,tblspecialty spec, tblindividualspecialty indspec      ind.pkindividualid = indspec.fkindividualid , spec.specialtycode = indspec.specialtycode , pkindividualid = 355735; 

i received format result

but need format result

can 1 me please. using mysql database , development tool visual studio 2013.

part of problem mysql not have equivalent of sql's pivot, , in overcoming doesn't have built-in concept of rownum.

i created tables 2 separate individuals, 4 specialities of first individual had 3 , second had 2. combined these interim table (temporary table if prefer) using

insert interimresults ( select @row_number:=case when @indid=pkindividualid                     @row_number+1 else 1 end row_number,        @indid:=pkindividualid pkindividualid, firstname, lastname,        specialityname (select @rownum:=0) n,       (select @db_names:='') d,       (select pkindividualid,firstname,lastname,specialityname       tblindividual ind       inner join tblindividualspeciality indspec on ind.pkindividualid=indspec.fkindividualid       inner join tblspeciality spec on spec.pkspecialitycode=indspec.fkspecialitycode) r ); 

this gave me following results (with test data). note if run in sqlfiddle's "build schema" pane of row numbers come out 1, query works correctly in "run sql" panel.

row_number pkindividualid firstname lastname   specialityname 1          1              test      i-health   internal medecine  2          1              test      i-health   family practise  3          1              test      i-health   cardiology  1          2              test2     j-health   family practise  2          2              test2     j-health   obstetrics  

it quite simple "pivot" interim table using group_concat

select     firstname, lastname,     group_concat(if(row_number = 1, specialityname, null)) 'speciality1',    group_concat(if(row_number = 2, specialityname, null)) 'speciality2',    group_concat(if(row_number = 3, specialityname, null)) 'speciality3'  interimresults  group pkindividualid; 

references used: http://www.artfulsoftware.com/infotree/qrytip.php?id=78 , http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

of course assumes want report on first 3 specialities, or each individual can have 3 specialities - if there others need use prepared statements , dynamic sql - there example at http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/