Converting columns in to rows in SQL SERVER

Question

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 6-Oct-18 21:06pm

Member 14010375

Updated 6-Oct-18 22:31pm

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-10-06T22:10:00

Solution 1

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!

Posted 6-Oct-18 22:10pm

OriginalGriff

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 :)

Converting columns in to rows in SQL SERVER

1 solution

Solution 1

Add your solution here

Preview 0

Converting columns in to rows in SQL SERVER

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us