如何使用SQL在列中获取数据 [英] How do is get data in column using SQL
问题描述
您好，这里所有开发人员的好日子
我想知道如何从不同行的表中获取数据和
在列中显示数据
我有一张包含以下字段的表格。
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')
推荐答案
您好，
请尝试以下代码，它可以解决你的问题。如果您遇到任何问题，请告诉我。
选择t1.Name，t1.Date，t1.TimeCL为'Time IN '，t2.TimeCL为'超时'
来自表t1的
连接t1.Date = t2.Date上的表t2
其中t1.Details ='IN'和t2 .Details ='OUT'
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'
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在列中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
