How do is get data in column using SQL

Question

0.00/5 (No votes)

See more:

Hi Good day to all developers here
I want to know how to get data from table that are in different Rows and
Show that data in Columns

I have a table with following fields.

Name	Date	        TimeCL	               Detail
Rashid Ali	2017-12-05	09:22:56.0000000	IN
Rashid Ali	2017-12-05	10:14:33.0000000	OUT
Rashid Ali	2017-12-14	09:00:45.0000000	IN
Rashid Ali	2017-12-14	14:08:53.0000000	OUT

and I Want to Get Data Like This.

Name	Date	        Time IN                 Time Out
Rashid Ali	2017-12-05	09:22:56.0000000      10:14:33.0000000	
Rashid Ali	2017-12-14	09:00:45.0000000      14:08:53.0000000

Can anyone give me a hand for me to have a good start

Thanks

What I have tried:

I try This But It Give me wrong Data

Quote:
SELECT Std_InfoTB.Name, AttendanceTb.Date, AttendanceTb.TimeCL, AttendanceTb_1.TimeCL AS Expr1
FROM Std_InfoTB INNER JOIN
AttendanceTb ON Std_InfoTB.Std_ID = AttendanceTb.Std_ID INNER JOIN
AttendanceTb AS AttendanceTb_1 ON Std_InfoTB.Std_ID = AttendanceTb_1.Std_ID
WHERE (AttendanceTb.Std_ID = 21) AND (MONTH(AttendanceTb.Date) = 12) AND (YEAR(AttendanceTb.Date) = 2017) AND (AttendanceTb.Detail = N'IN') AND
(AttendanceTb_1.Detail = N'OUT')

Posted 26-Dec-17 6:18am

RASHID ALI786

Updated 3-Jan-18 11:39am

v2

Add a Solution

3 solutions

Solution 1

Hi,

Try the below code, it may resolve your problem. If you face any issue then let me know.

select t1.Name,t1.Date,t1.TimeCL as 'Time IN', t2.TimeCL as 'Time OUT'
from Table t1 join Table t2 on t1.Date=t2.Date
where t1.Details='IN' and t2.Details='OUT'

Posted 26-Dec-17 10:22am

Rajesh Ku Swain

Comments

RASHID ALI786 27-Dec-17 9:40am

Hello Rajesh.
I tried that but it didn't work...

I have an attendance table and it has 16 records of a student having ID 21
But using above Query it gives me more than that...

Original Record
Atd_ID Std_ID Date Detail TimeCL MessageIN MessageOut
88 21 2017-12-04 OUT 13:55:02.0000000 0 1
93 21 2017-12-05 IN 09:22:56.0000000 1 0
96 21 2017-12-05 OUT 10:14:33.0000000 0 1
98 21 2017-12-14 IN 09:00:45.0000000 1 0
106 21 2017-12-14 OUT 14:08:53.0000000 0 1
114 21 2017-12-15 IN 09:30:48.0000000 1 0
115 21 2017-12-15 OUT 10:03:14.0000000 0 1
125 21 2017-12-16 IN 09:34:05.0000000 1 0
127 21 2017-12-16 OUT 11:46:00.0000000 0 1
128 21 2017-12-18 IN 08:53:55.0000000 1 0
137 21 2017-12-18 OUT 14:18:11.0000000 0 1
142 21 2017-12-19 IN 08:51:36.0000000 1 0
147 21 2017-12-19 OUT 14:00:18.0000000 0 1
152 21 2017-12-20 IN 09:10:32.0000000 1 0
156 21 2017-12-20 OUT 13:10:52.0000000 0 1
164 21 2017-12-21 IN 09:11:22.0000000 1 0

Extracted Record

Std_ID Date Time IN Time OUT
21 2017-12-05 09:22:56.0000000 10:14:33.0000000
21 2017-12-05 09:22:56.0000000 13:30:11.0000000
21 2017-12-14 09:00:45.0000000 14:07:03.0000000
21 2017-12-14 09:00:45.0000000 14:08:53.0000000
21 2017-12-14 09:00:45.0000000 14:16:17.0000000
21 2017-12-14 09:00:45.0000000 16:55:39.0000000
21 2017-12-15 09:30:48.0000000 10:03:14.0000000
21 2017-12-15 09:30:48.0000000 10:03:20.0000000
21 2017-12-15 09:30:48.0000000 10:03:30.0000000
21 2017-12-15 09:30:48.0000000 12:30:18.0000000
21 2017-12-15 09:30:48.0000000 12:55:09.0000000
21 2017-12-15 09:30:48.0000000 14:55:08.0000000
21 2017-12-16 09:34:05.0000000 11:46:00.0000000
21 2017-12-18 08:53:55.0000000 13:19:44.0000000
21 2017-12-18 08:53:55.0000000 14:10:39.0000000
21 2017-12-18 08:53:55.0000000 14:18:11.0000000
21 2017-12-18 08:53:55.0000000 14:36:09.0000000
21 2017-12-19 08:51:36.0000000 14:00:18.0000000
21 2017-12-19 08:51:36.0000000 14:00:26.0000000
21 2017-12-19 08:51:36.0000000 14:00:36.0000000
21 2017-12-20 09:10:32.0000000 13:10:52.0000000
21 2017-12-20 09:10:32.0000000 15:56:56.0000000
21 2017-12-20 09:10:32.0000000 16:43:15.0000000
21 2017-12-21 09:11:22.0000000 12:33:04.0000000
21 2017-12-21 09:11:22.0000000 12:58:44.0000000

As you see in the original record there are two rows of the same date, One is for "TimeIN" And One is for "TimeOut" and I want to show record in one Row with "TimeIN" and "TimeOut" in the different columns...

Hope you understand what I am trying to say....

Thanks.

Solution 2

This is a simple Pivot solution. There is an excellent article at:
https://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

Don't split the date and time into separate columns, it is bad practice. Other than that, here is a simple solution:

DECLARE @T TABLE(
	Atd_ID	int,
	Std_ID int,
	transDate date,
	Detail nvarchar(10),
	TimeCL time,
	MessageIN bit,
	MessageOut bit
)

INSERT INTO @T VALUES
(88,21,'2017-12-04','OUT','13:55:02.0000000',0,1),
(93,21,'2017-12-05','IN','09:22:56.0000000',1,0),
(96,21,'2017-12-05','OUT','10:14:33.0000000',0,1),
(98,21,'2017-12-14','IN','09:00:45.0000000',1,0),
(106,21,'2017-12-14','OUT','14:08:53.0000000',0,1),
(114,21,'2017-12-15','IN','09:30:48.0000000',1,0),
(115,21,'2017-12-15','OUT','10:03:14.0000000',0,1),
(125,21,'2017-12-16','IN','09:34:05.0000000',1,0),
(127,21,'2017-12-16','OUT','11:46:00.0000000',0,1),
(128,21,'2017-12-18','IN','08:53:55.0000000',1,0),
(137,21,'2017-12-18','OUT','14:18:11.0000000',0,1),
(142,21,'2017-12-19','IN','08:51:36.0000000',1,0),
(147,21,'2017-12-19','OUT','14:00:18.0000000',0,1),
(152,21,'2017-12-20','IN','09:10:32.0000000',1,0),
(156,21,'2017-12-20','OUT','13:10:52.0000000',0,1),
(164,21,'2017-12-21','IN','09:11:22.0000000',1,0);

SELECT * FROM 
(SELECT Std_ID, transDate, MessageIN, TimeCL  FROM @T)
AS s
PIVOT
(
	MAX(TimeCL)
	FOR MessageIN IN ([1],[0])
) AS p

Your results will look like this:

Std_ID	transDate	1	0
21	2017-12-04	NULL	13:55:02.0000000
21	2017-12-05	09:22:56.0000000	10:14:33.0000000
21	2017-12-14	09:00:45.0000000	14:08:53.0000000
21	2017-12-15	09:30:48.0000000	10:03:14.0000000
21	2017-12-16	09:34:05.0000000	11:46:00.0000000
21	2017-12-18	08:53:55.0000000	14:18:11.0000000
21	2017-12-19	08:51:36.0000000	14:00:18.0000000
21	2017-12-20	09:10:32.0000000	13:10:52.0000000
21	2017-12-21	09:11:22.0000000	NULL

I hope this meets your requirements.

Posted 3-Jan-18 11:39am

Mike Ahrens

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santosh kumar Pithani · Accepted Answer · 2018-01-03T17:21:00

CREATE TABLE #Temp(Name Varchar(50),Dts Date,TimeCL Time,Detail varchar(20));

INSERT INTO #TEMP 
                 VALUES('Rashid Ali','2017-12-05','09:22:56.0000000','IN'),
                       ('Rashid Ali','2017-12-05','10:14:33.0000000','OUT'),
                       ('Rashid Ali','2017-12-14','09:00:45.0000000','IN'),
                       ('Rashid Ali','2017-12-14','14:08:53.0000000','OUT');
                 
SELECT Name,
       Dts,
	   MAX(CASE WHEN Detail='IN' THEN TIMECl END) AS TimeIN,
	   MAX(CASE WHEN Detail='out' THEN TIMECl END) AS TimeOut 
  FROM #Temp GROUP BY Name,Dts;

OUTPUT:-
----------------------------------------------------------
 Name	       Dts	     TimeIN	           TimeOut
 -------------------------------------------------------------
Rashid Ali	2017-12-05	09:22:56.0000000	10:14:33.0000000
Rashid Ali	2017-12-14	09:00:45.0000000	14:08:53.0000000