How To Display Sql Data In A Specific Format In Html

I have data in Mysql in this format : name sub ---------------- a maths a science a history b maths b science a computer a english

Solution 1:

If you want to dynamically create column, you can use dynamic pivot.

use GROUP_CONCAT to create your pivot column, then Concat the SQL execute syntax, execute it dynamically.

SET@sql=NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COALESCE(MAX(CASE WHEN sub = ''',
      sub,
      ''' then ''y'' end),''n'') AS ',
      sub
    )
  ) INTO@sqlFROM T;

SET@sql= CONCAT('SELECT name, ', @sql, ' 
                  FROM T 
                  GROUP BY name');

PREPARE stmt FROM@sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;

sqlfiddle

Result

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

Solution 2:

I found a similar case here. Let me brief a little bit of the problem and solution:

Problem:

Convert this:

select*from history;

+--------+----------+-----------+| hostid | itemname | itemvalue |+--------+----------+-----------+|1| A        |10||1| B        |3||2| A        |9||2| C        |40|+--------+----------+-----------+

Into this:

select*from history_itemvalue_pivot;

+--------+------+------+------+| hostid | A    | B    | C    |+--------+------+------+------+|1|10|3|0||2|9|0|40|+--------+------+------+------+

Answer :

From the article, here are the steps the author did:

1. select the columns of interest, i.e. y-values and x-values
2. extend the base table with extra columns -- one for each x-value
3. group and aggregate the extended table -- one group for each y-value
4. (optional) prettify the aggregated table

Here is the full article: MySQL - Rows to Columns

Hope it helps.

Regards,

Solution 3:

You can use condition aggregation in SQL :

SELECT name,  
       MAX(CASEWHEN sub ='maths'then'y'ELSE'n'END) AS maths,
       MAX(CASEWHEN sub ='science'then'y'ELSE'n'END) AS science,
       MAX(CASEWHEN sub ='history'then'y'ELSE'n'END) AS history,
       MAX(CASEWHEN sub ='computer'then'y'ELSE'n'END) AS computer,
       MAX(CASEWHEN sub ='english'then'y'ELSE'n'END) AS english
FROMtable t
GROUPBY name;