How to display sql data in a specific format in html

Question

I have data in Mysql in this format :

   name sub
----------------
    a   maths
    a   science
    a   history
    b   maths
    b   science
    a   computer
    a   english
    c   computer
    c   history
    b   history
    c   maths

I am planning to display this data in this format in HTML:

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

How to formulate my sql query other than pivot table method?

You mention html. Presumably, you have some layer that transcribes the raw data to html. If it was me I'd handle the logic of missing results, and tabulation, there. — Strawberry, Aug 28 '18 at 16:47

score 1 · Answer 1 · answered Aug 28 '18 at 16:52

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 @sql
FROM T;

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

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE 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

answered Aug 28 '18 at 16:52

D-Shih

44,943
6
31
51

How to use Y or N value from another column instead of hardcoding it. – qwww Aug 29 '18 at 09:02
@qwww Is the value contain in other table? you can use dynamic sql to get the value. – D-Shih Aug 31 '18 at 20:12
same table..but different column – qwww Sep 01 '18 at 06:03
@qwww But How did you know which state mean which value? For this example How did you know if there get `sub` mean `Y` else `N` – D-Shih Sep 01 '18 at 07:15

Ivan Contreras · Answer 2 · 2018-08-29T14:28:36.210

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 |
+--------+------+------+------+

Solution:

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

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

Here is the full article: MySQL - Rows to Columns

Hope it helps.

Regards,

Yogesh Sharma · Answer 3 · 2018-08-28T16:47:40.717

0

You can use condition aggregation in SQL :

SELECT name,  
       MAX(CASE WHEN sub = 'maths'   then 'y' ELSE 'n' END) AS maths,
       MAX(CASE WHEN sub = 'science' then 'y' ELSE 'n' END) AS science,
       MAX(CASE WHEN sub = 'history' then 'y' ELSE 'n' END) AS history,
       MAX(CASE WHEN sub = 'computer'then 'y' ELSE 'n' END) AS computer,
       MAX(CASE WHEN sub = 'english' then 'y' ELSE 'n' END) AS english
FROM table t
GROUP BY name;

edited Aug 28 '18 at 16:47

answered Aug 28 '18 at 16:41

Yogesh Sharma

49,870
5
26
52

what if the number of subjects are dynamic? – qwww Aug 30 '18 at 05:46

How to display sql data in a specific format in html

3 Answers3

Linked