获取SQL表另一列中的对应记录 [英] Get corrosponding record Of Row in Another Column of SQL Table

问题描述

我有一个 SQL 表，其中每天存储/保存员工的记录我想以表格格式获得该结果

I am having SQL table where the records of the employee on daily basis are stored/saved I would like to get that result in tabular format

考虑如下所示的表格

 NAME     | DATEANDTIME         | ACTION 
----------|---------------------|-----------
JOHN      | 2019-07-07 10:00:00 |   INTIME
JOHN      | 2019-07-07 18:00:00 |   OUTTIME
WILLIAM   | 2019-07-07 10:02:00 |   INTIME
CATHERIN  | 2019-07-07 10:10:00 |   INTIME
JOHN      | 2019-07-07 13:00:00 |   LUNCH
CATHERIN  | 2019-07-07 18:30:00 |   OUTTIME
CATHERIN  | 2019-07-07 14:30:00 |   LUNCH
WILLIAM   | 2019-07-07 19:14:00 |   INTIME

我希望上面的表格以垂直格式显示，因为它应该按照名称分组

I want above table to be displayed in the vertical format as it should be group as per the name

我曾尝试使用数据透视查询，但它没有给我预期的输出

I had tried using the pivot query but it is not giving me expected output

 select
    Dateandtime,
    [name],
    [action]
from
    (
        select Dateandtime,
        [name],
        [action]
    from
        table1) a pivot(max(action) for tagindex in (Dateandtime,
    [name],
    [action]))pvt


   INTIME            |      OUTTIME          | LUNCH               |  NAME
---------------------|-----------------------|---------------------|--------
#2019-07-07 10:00:00 | 2019-07-07 18:00:00   |        -------      | WILLIAM                                                    
#2019-07-07 10:10:00 | 2019-07-07 18:30:00   | 2019-07-07 14:30:00 |CATHERIN

推荐答案

我使用了一个 CTE，它对我有用，如下

I had used a CTE which is worked for me and is as follows

WITH cte 
AS (SELECT Row_number() 
OVER( 
partition BY [name] 
ORDER BY [name]) AS rn, *FROM   table1)           
SELECT a.[dateandtime]  AS [IN], 
b.[dateandtime]  AS [OUT], 
a.[action]  AS [myaction], 
   c.[dateandtime]  AS [lunchTIME] 
FROM   (SELECT * 
FROM   cte a 
WHERE  rn = 1) a 
JOIN (SELECT * 
FROM   cte b 
WHERE  rn = 2) b 
ON a.[name] = b.[name] 
LEFT JOIN (SELECT * 
FROM   cte b 
WHERE  rn = 3) c 
ON a.[name] = c.[name]  

这篇关于获取SQL表另一列中的对应记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！