Create dynamic column based on data

Question

I have 2 table grade and tb_category

CREATE TABLE `grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentid` int(11) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `grade` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 
INSERT INTO `grade` (`id`, `studentid`, `category`, `grade`) VALUES
(1, 1,  'PAS',  76),
(2, 1,  'PTS',  100),
(3, 2,  'PAS',  100),
(4, 3,  'PTS',  100),
(5, 4,  'PTS',  100),
(6, 4,  'PAS',  100);


CREATE TABLE `tb_category` (
  `gradecatid` int(11) NOT NULL DEFAULT 0,
  `code` varchar(3) DEFAULT NULL,
  `category_name` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `tb_category` (`gradecatid`, `code`, `category_name`) VALUES
(1, 'PTS',  'Mid-Semester'),
(2, 'PAS',  'End Semester');

with data:

id  studentid   category    grade
1   1   PAS 76
2   1   PTS 100
3   2   PAS 100
4   3   PTS 100
5   4   PTS 100
6   4   PAS 100


gradecatid  code    category_name
1   PTS Mid-Semester
2   PAS End Semester

the expect result is dynamic column base on category column

studentid	PAS	PTS
1	76	100
2	100	0
3	0	100
4	100	100

I have the query for result

SELECT studentid,
SUM(IFNULL(IF(category = 'PAS', grade, 0), 0)) AS grade1,
SUM(IFNULL(IF(category = 'PTS', grade, 0), 0)) AS grade2
FROM `grade`
group by studentid

the user can add or delete the category (PAS, PTS), so above query will not work if there's any changes with the category, every user can have multiple category, is it possible to have dynamic column based on the user?

studentid	grade1	grade2	grade3	grade4
1	76	100	?	?
2	100	0	?	?
3	0	100	?	?
4	100	100	?	?

" is it possible to have dynamic column based on the user?" Short answre: No, longer answer see: [MySQL pivot table query with dynamic columns](https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) — Luuk, Mar 11 '23 at 08:33
ok, thank you for the reference, I will try use stored procedure — bagi2info.com, Mar 11 '23 at 14:32
You don't need to use a stored procedure. You can do it with queries directly from your client application. There are many examples posted, try a search for [`[mysql] [pivot-table]`](https://stackoverflow.com/questions/tagged/mysql+pivot-table) — Bill Karwin, Mar 11 '23 at 17:29
Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — June7, Mar 11 '23 at 20:00
right now, still trying using SP for this issue as suggestion from @Luuk, yes I can use client application but we will doing it SP directly — bagi2info.com, Mar 12 '23 at 05:34

Create dynamic column based on data

0 Answers0