Pivot Table Problem

[getnitha]

hai

i got a table having the structure as

D_EVENT_DATE S_USER_ID N_EVENT


08/02/07 08:01:46 R12 16
08/02/07 13:01:58 R12 18
08/02/07 15:02:06 R12 19
08/02/07 20:02:15 R12 17
08/03/07 08:30:00 120 16
08/03/07 13:42:14 120 18
08/03/07 16:22:35 120 19
08/03/07 19:00:49 120 17


I NEED TO RETREIVE THIS WHOLE DATA AND INSERT INTO NEW TABLE
ITS FORMAT IS LIKE THIS

User_ID PUNCH_DATE TIME_IN BREAK_OUT BREAK_IN TIME_OUT
R12 08/02/07 08:01:46 13:01:58 15:02:06 20:02:15
120 08/03/07 08:30:00 13:42:14 16:22:35 19:00:49


I WROTE A QUERY LIKE THIS BIT ITS INSERTING 9 ROWS INSTEAD OF 3.SO ANYONE PLEASE HELP ME TO SOLVE THIS PROBLEM BY CORRECTING MY QUERY

insert into ATTENDENCE_REPORT(User_ID,User_Name,Punch_Date,Time_In,Break_Out,Break_In,Time_Out)

SELECT
USER_CREATION.User_ID,USER_CREATION.User_Name ,
substring(convert(nvarchar(50),D_Event_Date),0,13),
(CASE t_tna_log.n_event WHEN 16 THEN substring(convert(nvarchar(50),D_Event_Date),13,11) ELSE '0' END) AS Time_In,
(CASE t_tna_log.n_event WHEN 18 THEN substring(convert(nvarchar(50),D_Event_Date),13,11) ELSE '0' END) AS Break_Out,
(CASE t_tna_log.n_event WHEN 19 THEN substring(convert(nvarchar(50),D_Event_Date),13,11) ELSE '0' END) AS Break_In,
(CASE t_tna_log.n_event WHEN 17 THEN substring(convert(nvarchar(50),D_Event_Date),13,11) ELSE '0' END) AS Time_Out
FROM t_tna_log,USER_CREATION where USER_CREATION.User_Id = t_tna_log.s_user_id
group BY t_tna_log.s_User_ID
--USER_CREATION.User_Name ,substring(convert(nvarchar(50),D_Event_Date),0,13),t_tna_log.n_event,t_tna_log.D_EVENT_DATE

 

[cjard]

erm.. why dont you use the built in PIVOT ?

http://www.devx.com/dbzone/Article/28165