CREATE TABLE [dbo].[multiple](
[emp_code] [varchar](20) NULL,
[swip_in] [nvarchar](255) NULL,
[swipe_in_date] [nvarchar](255) NULL,
[swipe_out] [nvarchar](255) NULL,
[Swipe_out_date] [nvarchar](255) NULL
) ON [PRIMARY]

insert into multiple values ('H10004','04:48:34','08/12/2015','12:00:42','08/12/2015')
insert into multiple values ('H10004','14:36:30','08/12/2015','18:50:16','08/12/2015')
insert into multiple values ('H10005','05:50:08','08/12/2015','11:50:58','08/12/2015')
insert into multiple values ('H10005','14:48:06','08/12/2015','18:52:34','08/12/2015')
insert into multiple values ('H10005','20:22:56','08/12/2015','22:56:10','08/12/2015')
insert into multiple values ('H10009','05:45:44','08/12/2015','11:53:46','08/12/2015')
insert into multiple values ('H10009','14:52:52','08/12/2015','18:55:04','08/12/2015')
insert into multiple values ('H10013','05:57:32','08/12/2015','12:00:52','08/12/2015')
insert into multiple values ('H10013','14:46:44','08/12/2015','18:48:02','08/12/2015')

i want result like below 

Empcode swip_in1,   swip_out1,    swip_in2,   swip_out2,   swip_in3,   swip_out3

Hi Udhuman,

Find the below query

 WITh T1 AS(
SELECT emp_code,swipe_out1,swipe_out2,swipe_out3 FROM
(
SELECT emp_code, col, val FROM(
  SELECT *, 'swip_in'+ CONVERT(varchar,ROW_NUMBER()OVER(PARTITION BY emp_Code ORDER BY emp_code))  as Col, swip_in as Val FROM multiple
 UNION
 SELECT *, 'swipe_out'+ CONVERT(varchar,ROW_NUMBER()OVER(PARTITION BY emp_Code ORDER BY emp_code)) as Col,swipe_out as Val  FROM multiple
) t
) tt
PIVOT ( min(val) for Col in ([swipe_out1],[swipe_out2],[swipe_out3]) ) AS pvt),
T2 AS(
SELECT emp_code,swip_in1,swip_in2,swip_in3 FROM
(
SELECT emp_code, col, val FROM(
 SELECT *, 'swip_in'+ CONVERT(varchar,ROW_NUMBER()OVER(PARTITION BY emp_Code ORDER BY emp_code))  as Col, swip_in as Val FROM multiple
 UNION
 SELECT *, 'swipe_out'+ CONVERT(varchar,ROW_NUMBER()OVER(PARTITION BY emp_Code ORDER BY emp_code)) as Col,swipe_out as Val  FROM multiple
) t
) tt
PIVOT ( max(val) for Col in (swip_in1,swip_in2,swip_in3) ) AS pvt)
SELECt T1.emp_code,T2.swip_in1,T1.swipe_out1,T2.swip_in2,T1.swipe_out2,T2.swip_in3,T1.swipe_out3 FROM T1 INNER JOIN T2 ON  T1.emp_code= T2.emp_code

Try

CREATE TABLE #multiple(
[emp_code] [varchar](20) NULL,
[swipe_in] time NULL,
[swipe_in_date] date NULL,
[swipe_out] time NULL,
[Swipe_out_date] date NULL
) ON [PRIMARY]


insert into #multiple values ('H10004','04:48:34','08/12/2015','12:00:42','08/12/2015')
insert into #multiple values ('H10004','14:36:30','08/12/2015','18:50:16','08/12/2015')
insert into #multiple values ('H10005','05:50:08','08/12/2015','11:50:58','08/12/2015')
insert into #multiple values ('H10005','14:48:06','08/12/2015','18:52:34','08/12/2015')
insert into #multiple values ('H10005','20:22:56','08/12/2015','22:56:10','08/12/2015')
insert into #multiple values ('H10009','05:45:44','08/12/2015','11:53:46','08/12/2015')
insert into #multiple values ('H10009','14:52:52','08/12/2015','18:55:04','08/12/2015')
insert into #multiple values ('H10013','05:57:32','08/12/2015','12:00:52','08/12/2015')
insert into #multiple values ('H10013','14:46:44','08/12/2015','18:48:02','08/12/2015')

;with cte as (select emp_code, convert(datetime, convert(datetime,swipe_in_date)   + swipe_in) as swipe_in,
convert(datetime, convert(datetime,swipe_out_date) +  swipe_out) as swipe_out
from #multiple)

select emp_code,
max(case when Rn=1 then swipe_in end) as swipe_in1,
max(case when Rn=1 then swipe_out end) as swipe_out1,
max(case when Rn=2 then swipe_in end) as swipe_in2,
max(case when Rn=2 then swipe_out end) as swipe_out2,
max(case when Rn=3 then swipe_in end) as swipe_in3,
max(case when Rn=3 then swipe_out end) as swipe_out3
from
(select *, row_number() over (partition by emp_code order by swipe_in, swipe_out) as Rn from cte) src
GROUP BY emp_code
ORDER BY emp_code

I hope your real table uses correct types for date and time values and not what you showed.

Hi Mohamed,

You can try below sample as well.

;WITH Cte AS
(
SELECT emp_code,CAST(swipe_in_date+' '+swip_in AS DATETIME) swip_in, CAST(swipe_out_date+' '+swipe_out AS DATETIME) swip_out,
ROW_NUMBER() OVER(PARTITION BY emp_code ORDER BY CAST(swipe_in_date+' '+swip_in AS DATETIME)) rn FROM multiple
) 
SELECT c.emp_code,c.swip_in swip_in1,c.swip_out swip_out1,c2.swip_in swip_in2,c2.swip_out swip_out2,c3.swip_in swip_in3,c3.swip_out swip_out3 FROM Cte c
LEFT JOIN Cte c2 ON c.rn=1 AND c2.rn=2 AND c.emp_code=c2.emp_code
LEFT JOIN Cte c3 ON c.rn=1 AND c3.rn=3 AND c.emp_code=c3.emp_code
WHERE c.rn=1

If you have any question, feel free to let me know.