Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have a table having records are saving in this format

EMPCODE PUNCH_DATE PUNCH_TYPE
E101 2020-07-25 15:44:23 IN
E101 2020-07-25 15:45:14 IN
E101 2020-07-25 15:45:43 IN
E101 2020-07-25 15:46:07 IN
E101 2020-07-25 15:47:14 OUT
E101 2020-07-25 15:47:22 IN
E101 2020-07-25 15:48:27 OUT
E101 2020-07-25 15:49:58 OUT
E101 2020-07-25 15:50:19 IN

but i need data in one row of a single date. kindly provide the sql query to show the above data in below format.

EMPCODE	IN	IN	IN	IN	OUT	IN	OUT	OUT	IN
E101	2020-07-25 15:44:23	2020-07-25 15:45:14	2020-07-25 15:45:43	2020-07-25 15:46:07	2020-07-25 15:47:14	2020-07-25 15:47:22	2020-07-25 15:48:27	2020-07-25 15:49:58	2020-07-25 15:50:19


What I have tried:

i try to use pivot option but could't successes
Posted
Updated 30-Jul-20 1:10am
Comments
Garth J Lancaster 30-Jul-20 7:14am    
"i try to use pivot option but could't successes" - what does that mean ? since you don't post code how do you expect us to help - non-posted code is a) vapourware, b) B/S (I'll let you figure that one out) .. please provide more info so we can help you better
Member 10438440 30-Jul-20 7:43am    
Ok. I have sql table EMPPUNCH_MASTER having column names are as - EMPCODE, PUNCH_DATE, PUNCH_TYPE

Record in the table is in below format -

EMPCODE PUNCH_DATE PUNCH_TYPE
E101 2020-07-25 15:44:23 IN
E101 2020-07-25 15:45:14 IN
E101 2020-07-25 15:47:14 OUT
E101 2020-07-25 15:47:22 IN
E101 2020-07-25 15:48:27 OUT


but i require record in below format -
EMPCODE IN IN OUT
E101 2020-07-25 15:44:23 2020-07-25 15:45:14 2020-07-25 15:47:14

here, one employee record shown in one row. i want this.
i try several options but couldn't find any solution.

1 solution

Sounds like you need a SQL PIVOT.

Read about SQL PIVOT:
MSDN: Using PIVOT and UNPIVOT[^]
Understanding PIVOT and UNPIVOT[^]
Pivoting data in SQL Server[^]

Try out!
 
Share this answer
 
Comments
Member 10438440 30-Jul-20 7:24am    
yes i need to transpose rows into column. like
EMPCODE IN IN OUT
E101 punchtime punchtime punchtime
Member 10438440 30-Jul-20 7:26am    
aggregate function makes issue.
Sandeep Mewara 30-Jul-20 7:28am    
You have to share the details of the issue you are facing so anyone to help.

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