Click here to Skip to main content
14,743,899 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi, I have a table like below.

nUserID	sDate	IN_FINGER	OUT_FINGER
4431	2018/10/03	08:33	16:17
4513	2018/10/03	05:11	16:03
4431	2018/10/04	08:30	16:20
4513	2018/10/04	04:27	15:45
4513	2018/10/06	05:41	14:03


I want to convert columns into rows like below

nUserID	sDate	Stime	Ttype
4431	10/3/2018	8:33	IN
4431	10/3/2018	16:17	OUT
4513	10/3/2018	5:11	IN
4513	10/3/2018	16:03	OUT
4431	10/4/2018	8:30	IN
4431	10/4/2018	16:20	OUT
4513	10/4/2018	4:27	IN
4513	10/4/2018	15:45	OUT
4513	10/6/2018	5:41	IN
4513	10/6/2018	14:03	OUT


How can i achieve this?

What I have tried:

I have tried with UNPIVOTING, but I am not an expert in querying so I could not get the result I was looking for.

Regards
Bibin
Posted
Updated 6-Oct-18 23:31pm

1 solution

We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.

So start here: Using PIVOT and UNPIVOT | Microsoft Docs[^] and learn how to do it!
Try it yourself, you may find it is not as difficult as you think!

If you meet a specific problem, then please ask about that and we will do our best to help. But we aren't going to do it all for you!
   
Comments
Member 14010375 7-Oct-18 4:18am
   
Actually, I am a project coordinator, not a software professional. Anyways I will try to do as per the document you suggested, thank you!
Member 14010375 7-Oct-18 4:28am
   
And I have managed to get the result but was not able to get the last column TType

select nUSerID, Sdate, Stime
from TA_FINGER_ONLY
unpivot
(
Stime
for TTYPE in (IN_FINGER,OUT_FINGER)
) u
Member 14010375 8-Oct-18 3:38am
   
I got it


SELECT nUSerID, Sdate, Stime, 'IN' AS T_TYPE, nDepartmentIdn
FROM TA_FINGER_ONLY UNPIVOT (Stime FOR TTYPE1 IN (IN_FINGER)) u
UNION ALL
SELECT nUSerID, Sdate, Stime, 'OUT' AS T_TYPE, nDepartmentIdn
FROM TA_FINGER_ONLY UNPIVOT (Stime FOR TTYPE1 IN (OUT_FINGER)) u
OriginalGriff 8-Oct-18 3:46am
   
Well done!
Member 14010375 8-Oct-18 4:18am
   
Thank you :)

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