Select statement with dynamic columns where these columns are rows in another table

Question

I want to write a select statement that with some fixed columns and dynamic columns. here Dynamic Columns names are the rows in another table.

I have tried this but getting error. Please tell me where my error is

set @sql=(Select subject from tbl_subjects where C_Id=22);
SELECT 
    ta.rollno,
    ta.StdNm,
    tc.C_Name,
    @sql
FROM
    tbl_cmarks tm,
    tbl_admission ta,
    tbl_classes tc
WHERE
    tm.Cid = tc.C_Id
        AND ta.rollno = tm.rollno
        AND tm.Cid = 22 and tm.Examid=9 

Mysql showing:

Error Code: 1242. Subquery returns more than 1 row

Could any one tell me how to do this in mysql?

Answer 1

Looking at your table structures, I would suggest that you change the design to something that is normalized. For example:

create table tbl_subjects
(
  sub_id int,
  subject_name varchar(25)
);

create table tbl_cmarks
(
  c_id int,
  examid int,
  rollno int,
  sub_id int,
  mark int
);

Using something similar to above will allow you to add new subjects without having to alter your table. Then you will just join the tables on the sub_id to get the list of subjects for each class.

select ta.rollno,ta.StdNm,tc.C_Name,
  tm.mark,
  s.subject
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22 
  and tm.Examid=9;

See SQL Fiddle with Demo. The above will give you a result in rows, but you could easily apply an aggregate function with a CASE expression to pivot the data into columns. Similar to the following:

select ta.rollno,
  ta.StdNm,
  tc.C_Name,
  max(case when s.subject = 'English' then tm.mark end) Emglish,
  max(case when s.subject = 'Physics' then tm.mark end) Physics
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
  ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
  ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
  on tm.sub_id = s.sub_id
where tm.Cid = 22 
  and tm.Examid=9
group by  ta.rollno, ta.StdNm, tc.C_Name;

See Demo.

But if you don't change your current table structure and if you don't know the columns that you are going to return, then you will have to implement a prepared statement to generate dynamic SQL.

First, you will create the list of the classes:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject)) 
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

See Demo. This will give you the list of classes for each c_id. Once you have the list of classes, then you can add this to the rest of the sql string so the full code will be:

set @sqlList = null;
set @query = null;

SELECT
  GROUP_CONCAT(concat('tm.', subject)) 
INTO @sqlList
FROM tbl_subjects
where C_Id=22;

SET @query 
  = CONCAT('SELECT ta.rollno,ta.StdNm,tc.C_Name, ', @sqlList, ' 
            FROM tbl_cmarks tm
            INNER JOIN tbl_classes tc
              ON tm.Cid = tc.C_Id
            INNER JOIN tbl_admission ta
              ON ta.rollno = tm.rollno
            where tm.Cid = 22 
              and tm.Examid=9');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

Answer 2

Your first command set @sql=(Select subject from tbl_subjects where C_Id=22); gives you an error because subquery returns more than 1 row. You either need to use cursors, and iterate through resultset or do something like

set @sql=(Select GROUP_CONCAT(DISTINCT subject SEPARATOR  ',' ) 
 from tbl_subjects where C_Id=22);