Click here to Skip to main content
14,608,080 members
Rate this:
Please Sign up or sign in to vote.
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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!
   
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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100