问题描述
您好，这里所有开发人员的好日子
我想知道如何从不同行的表中获取数据和
在列中显示数据
我有一张包含以下字段的表格。
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
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'）
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')
推荐答案
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
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.
这篇关于如何使用SQL在列中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！