如何使用SQL查询在一行网格视图中显示2条记录 [英] How can we display 2 record in one row of grid view using SQL query

问题描述

你好，

我有一张表，...

中断和列名

BreakId bigint pk，BreakTypeIn int，BreakTimeIn datetime ，BreakTypeOut int，BreakTimeOut

datetime，CreateDateTime，UpdateDateTime。



日期26/10/2016有两条记录在那里。 />
BreakId BreakTypeIn BreakTimeIn BreakTypeOut BreakTimeOut CreateDateTime

1 1 12:03 2 12:13 2016-10-19 12：03：55.350

2 1 12 ：44 2 12:48 2016-10-19 12：44：48.813



所以第一次使用的时间是10分钟，第二次是4分钟。

我想在gridview的同一行显示这条记录。

Hello,
I have one table like,..
Break and column name
BreakId bigint pk,BreakTypeIn int,BreakTimeIn datetime,BreakTypeOut int,BreakTimeOut
datetime,CreateDateTime,UpdateDateTime.

on date 26/10/2016 two record are there.like
BreakId BreakTypeIn BreakTimeIn BreakTypeOut BreakTimeOut CreateDateTime
1 1 12:03 2 12:13 2016-10-19 12:03:55.350
2 1 12:44 2 12:48 2016-10-19 12:44:48.813

so First timebreak spent is 10 minutes and second is 4 minutes.
I want to display this record in gridview in same row like that..

StarofDay BreakIn  BreakOut BreakSpent BreakIn  BreakOut BreakSpent OutTime  TimeSpent
12:52PM	  12:03	    12:13   00:10	 12:44    12:48   00:04     4:21PM	03:15

Sub Total	                                                                03:15
Grand Total	                                                                03:15

怎样才能在gridview中获得此类输出。



我尝试过：

how can we get this kind output in gridview .

What I have tried:

CONVERT(varchar(5),ISNULL(DATEADD(minute, DATEDIFF(minute, AttendanceTimeIn, ISNULL(AttendanceTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0)-
         ISNULL(DATEADD(minute, DATEDIFF(minute, breakTimeIn, ISNULL(breakTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0)  AS [TimeSpentWork],
DATEDIFF(minute, AttendanceTimeIn, ISNULL(AttendanceTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30'')))-
          ISNULL(DATEDIFF(minute, breakTimeIn, ISNULL(breakTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))),0) ) AS [TimeSpentMinutesWork]

推荐答案

选择*从（选择不同的row_number（）结束（按列名称排序）

（

选择temp1.Column_Name +'，'AS [text（）]

来自#TEMP temp1

其中条件

ORDER BY列名

对于XML路径（''）

）[显示名称]

来自#TEMP temp2

）[主要]

Select * From ( Select distinct row_number() over(order by "Column Name"
(
Select temp1.Column_Name + ',' AS [text()]
From #TEMP temp1
Where "Condition"
ORDER BY "Column Name"
For XML PATH ('')
) ["Display Name"]
From #TEMP temp2
) [Main]

这篇关于如何使用SQL查询在一行网格视图中显示2条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！