There are two ways that I see you can do this - one would be with a static query the other with dynamic SQL.
Static query (even though you stated you have an unknown number of columns - this is how you would do this).:
select m.id,
min(case when [key] = 'X' then value else null end) as 'x',
min(case when [key] = 'y' then value else null end) as 'y',
min(case when [key] = 'z' then value else null end) as 'z'
from main m
left join child c
on m.id = c.mainid
group by m.id
see SQL Fiddle with Demo
Dynamic SQL version, this will create the query:
DECLARE @query AS NVARCHAR(MAX) = ' '
DECLARE @rowcount int
DECLARE @rowNumber int = 0
DECLARE @case as nvarchar(max) = ' '
DECLARE @keyValue as varchar(1)
select distinct [key]
into #temp
from child
-- get the number of unique keys
set @rowcount = (select count(*) from #temp)
while @rowNumber < @rowcount
begin
set @keyValue = (select top 1 [key] from #temp)
set @case
= @case + 'min(case when [key] = ''' + @keyValue
+ ''' then value else null end) as ''' + @keyValue + ''', '
if @rowNumber < @rowcount
begin
set @rowNumber = @rowNumber + 1
delete from #temp where [key] = @keyValue
end
end
set @query
= 'select m.id, '+ substring(@case, 1, len(@case) -1) + '
from main m
left join child c
on m.id = c.mainid
group by m.id'
exec(@query)
see SQL Fiddle with Demo
The static version is portable but will not work with your dynamic requirement.
This type of rotation of data from columns to rows is known as a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.
My first suggestion would be to determine if you have a calendar
table or a table that contains all of the dates that you want to display. If not, then I would suggest creating one similar to the following:
CREATE TABLE calendar (`Date` datetime) ;
INSERT INTO calendar (`Date`)
VALUES
('2013-06-01 00:00:00'),
('2013-06-02 00:00:00'),
('2013-06-03 00:00:00'),
('2013-06-04 00:00:00'),
('2013-06-05 00:00:00'),
('2013-06-06 00:00:00'),
('2013-06-07 00:00:00'),
('2013-06-08 00:00:00'),
('2013-06-09 00:00:00'),
('2013-06-10 00:00:00');
This will make it possible for you to generate a list of all of the dates that you want to display.
Second, you will need to generate the list of each student and each date. You can do this by using a CROSS JOIN between your tbl_admission
and the calendar
table:
select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a;
See Demo. Once you have this list, then you can use a LEFT JOIN to your existing tbl_absentees
table to get the result:
select
ca.studentname,
ca.rollno,
ca.class,
max(case when ca.date = '2013-06-01' then coalesce(p.status, 'P') end) `2013-06-01`,
max(case when ca.date = '2013-06-02' then coalesce(p.status, 'P') end) `2013-06-02`,
max(case when ca.date = '2013-06-03' then coalesce(p.status, 'P') end) `2013-06-03`,
max(case when ca.date = '2013-06-04' then coalesce(p.status, 'P') end) `2013-06-04`,
max(case when ca.date = '2013-06-05' then coalesce(p.status, 'P') end) `2013-06-05`,
max(case when ca.date = '2013-06-06' then coalesce(p.status, 'P') end) `2013-06-06`,
max(case when ca.date = '2013-06-07' then coalesce(p.status, 'P') end) `2013-06-07`,
max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-08`,
max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-09`,
max(case when ca.date = '2013-06-10' then coalesce(p.status, 'P') end) `2013-06-10`
from
(
select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a
) ca
left join tbl_absentees p
on ca.rollno = p.rollno
and ca.date = p.date
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno;
See SQL Fiddle with Demo. Of course for your request, you most likely want to query the data based on a date range so you will not want to hard-code the values. If that is the case, then you will need to look at using a prepared statement to generate dynamic SQL:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(CASE WHEN ca.date = ''',
date_format(date, '%Y-%m-%d'),
''' THEN coalesce(p.status, ''P'') END) AS `',
date_format(date, '%Y-%m-%d'), '`'
)
) INTO @sql
FROM calendar
where date>='2013-06-01'
and date <= '2013-06-05';
SET @sql
= CONCAT('SELECT ca.studentname,
ca.rollno,
ca.class, ', @sql, '
from
(
select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a
) ca
left join tbl_absentees p
on ca.rollno = p.rollno
and ca.date = p.date
where ca.date>=''2013-06-01''
and ca.date <= ''2013-06-05''
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo. Both of these queries will give a result similar to:
| STUDENTNAME | ROLLNO | CLASS | 2013-06-01 | 2013-06-02 | 2013-06-03 | 2013-06-04 | 2013-06-05 | 2013-06-06 | 2013-06-07 | 2013-06-08 | 2013-06-09 | 2013-06-10 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Naren | 1 | 22 | A | A | A | A | P | P | P | P | P | P |
| Srinu | 2 | 22 | P | P | P | P | P | P | P | P | P | P |
| Blah | 3 | 22 | A | P | P | P | P | P | P | P | P | P |
Best Answer
Unfortunately, MySQL does not have a PIVOT function and it sounds like you are attempting to get the data into the format:
If that is the case, then in MySQL you will want to use an aggregate function along with a CASE expression to convert your rows of data into columns.
If you have a know number of months that you are returning then you can easily hard-code the query similar to the following:
See SQL Fiddle with Demo.
But in your case it sounds like you want to change the start date and end date to your query. If that is the case, then you will want to use a prepared statement to generate dynamic SQL that would be executed:
See SQL Fiddle with Demo. Both queries will generate the table of data above.