Click here to Skip to main content
15,908,673 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,
for my application i have the following data
RF_Code                      Date           Time
104                        2013-01-01      09:00:00
104                        2013-01-01      18:12:00
104                        2013-01-02      09:02:00
104                        2013-01-02      19:05:00
105                        2013-01-01      09:30:00
105                        2013-01-01      18:30:00

i want to get the output as below
RF_Code     2013-01-01           2013-01-02 
104        09:00:00,18:12:00     09:02:00,19:05:00  
105        09:30:00,18:30:00      null,null

i written the following procedure and i am getting only one type of time only ie., By selecting min time or max time.

i want to concatenate both min time and max time with comma seperated
SQL
use ecampus;
DELIMITER $$
DROP PROCEDURE IF EXISTS `GetTimeByDate` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTimeByDate`(in sdate date)
BEGIN
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select RF_Code,";

declare curs cursor for select Date from ecampus.log where (month(Date)=month(sdate) AND YEAR(Date)=YEAR(sdate))  group by Date;
declare continue handler for not found set finish = 1;
open curs;
my_loop:loop
fetch curs into cdate;
if finish = 1 then
leave my_loop;
end if;
set str = concat(str, "min(case when Date = '",cdate,"' then Time else null end) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);

set @str = concat(str," from ecampus.log
            group by RF_Code");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
END $$

DELIMITER ;

i am getting output as below:
RF_Code     2013-01-01           2013-01-02
 104        09:00:00               09:02:00
 105        09:30:00               null
Posted
Updated 14-May-13 19:44pm
v2

1 solution

this is in MSSQL but this will give you an idea about dynamic pivot

http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900