如何合并来自同一个表的行 [英] how to merge rows from same table

问题描述

我想将同一个表的两列合并为一个，只显示选定的列。我有这样的sql表。

 S.no位置日期时间状态
 1 xyz 2014-6-6 10 ：55 in 
 2 abc 2014-6-6 4:30 out 
 3 mno 2014-6-7 11:00 in 
 4 mop 2014-6-7 4:00 out 
 5 abc 2014-6-8 11:00在
 

这里，我想合并列以显示基于相同日期的一列。所需格式为

 s.no LocationIn LocationOut date timeIN timeout 
 1 xyz abc 2014-6-6 10： 55 4:30 
 2 mno mop 2014-6-7 11:00 4:00 
 3 abc 2014-6-8 11:00 
 

i只使用了加入返回日期，但不知道怎么做..有谁能告诉我这是怎么做的？提前谢谢

解决方案

你可以在表上自己进行连接。



以下sql应该得到你想要的：

 选择 
 i.Location LocationIn，
 o.Location LocationOut，
 i。日期 日期，
 i。时间 TimeIn，
 o。时间 TimeOut 
 来自（选择 * 来自 TableName 其中 status = ' 在'）i 
 左 加入（选择 * 来自 TableName 其中 status = '  Out'）o 
  on  i。日期 = o。日期 

结果应该是这样的：

 LocationIn LocationOut 日期 TimeIn TimeOut 
 xyz abc 2014- 06-06 10:55 04:55 
 mop nop 2014-06-07 11:00 05:00 
 abc  NULL  2014-06- 08 11:00  NULL  

i和o是相关表的别名。



您应该考虑更改数据的存储方式，就像您想要跟踪特定日期的多个进入和退出状态一样。

我认为表格结构和日期连接不会给你所需的结果。

可能是一个额外的字段/列，称为轨道并在轨道上加入连接。



如果您添加额外记录'ppp'，'2014-06-06'，'5：55'，'Out'。

使用上述查询，您将获得两条日期为2014-06-06的记录。

 LocationIn LocationOut  Date  TimeIn TimeOut 
 xyz abc 2014-06-06 10:55 04:55 
 xyz ppp 2014-06-06 10:55 05:55 

我提供了一个解决方案，如果日期不重复，它将起作用，即超过2次，否则我们需要一些列来映射数据

  CREATE   TABLE  #MyLocation 
（SRNo  INT   IDENTITY ，位置< span class =code-keyword> VARCHAR （ 50 ），[日期] < span class =code-keyword> date ，[时间] 时间，状态< span class =code-keyword> Varchar （ 10 ））
 
  INSERT   INTO  #MyLocation  VALUES （'  xyz'，'  2014-06-06 '，'  10:55'， ' 在'）中，
（'  abc'，'  2014-06-06'，'  4:55'，'  out'），
（ '  mop'，'  2014-06-07'，'  11:00'，' 在'）中，
（ '  nop'，'  2014-06 -07'，'  5:00'，'  Out'）
 
 选择 *  FROM  #myLocation 
 
  select  row_number（） over （分区 按 [日期] 订单 < span class =code-keyword> by  Status）
  as  RN，Location，[日期]，[时间]，[状态] 
  INTO  #mydata 
 来自 #myLocation 
 
  SELECT  L1.Location   LocationIn'，L2.Location '  LocationOut'，L1。[ date ]，L1。[时间] '  TimeIn'，L2。[时间] '  TimeOut' 
  FROM  #mydata L1 
  INNER   JOIN  #mydata L2 
 < span class =code-keyword> ON  L1。[ date ] = L2。[ date ] 
  AND  L1.Location<> L2.Location 
  AND  L1。[Status] = ' 在' 

i wanted to merge two columns of same table into one and show only selected column. I have sql table like this.

S.no     Location      date           time            Status
  1        xyz       2014-6-6          10:55           In
  2        abc       2014-6-6           4:30           out
  3        mno       2014-6-7          11:00           In
  4        mop       2014-6-7           4:00           out
  5        abc       2014-6-8           11:00          In

Here, i wanted to merge columns to show one column based on same date. The required format is

s.no   LocationIn   LocationOut    date        timeIN   timeout
   1        xyz          abc           2014-6-6    10:55    4:30
   2        mno          mop           2014-6-7    11:00    4:00
   3        abc                        2014-6-8    11:00

i had used join to return date only but had no idea on how to do.. can anyone tell me how this could be done?? thanks in advance

解决方案

You can perform a join on the table to itself.

The following sql should get what you desire:

select 
	i.Location LocationIn,
	o.Location LocationOut,
	i.Date Date,
	i.Time TimeIn,
	o.Time TimeOut
from (select * from TableName where status = 'In') i
left join (select * from TableName where status = 'Out') o
	on i.Date = o.Date

The result should be something like:

LocationIn	LocationOut	Date	     TimeIn	TimeOut
xyz	        abc	        2014-06-06   10:55      04:55
mop	        nop	        2014-06-07   11:00      05:00
abc	        NULL            2014-06-08   11:00      NULL

The i and o are aliases for the table in question.

You should consider changing the way your data is stored as if you want to track more than one 'in' and 'out' status for a particular day.
I assume the table structure and the Date join will not give you the results you need.
Possibly an extra field/column called track and put a join on the track.

If you add an extra record 'ppp','2014-06-06','5:55','Out'.
Using the above query you would get two records with the Date 2014-06-06.

LocationIn	LocationOut	Date         TimeIn	TimeOut
xyz	        abc     	2014-06-06   10:55	04:55
xyz	        ppp	        2014-06-06   10:55	05:55

I am providing one solution it will work if dates are not repeating i.e. more than 2 time otherwise we need some column to map data

CREATE TABLE #MyLocation
(SRNo INT IDENTITY, Location VARCHAR(50),[Date] date ,[Time] time ,Status Varchar(10))

INSERT INTO  #MyLocation VALUES( 'xyz','2014-06-06','10:55','In'),
                    ( 'abc','2014-06-06','4:55','out'),
                    ( 'mop','2014-06-07','11:00','In'),
                    ( 'nop','2014-06-07','5:00','Out')

    select * FROM #myLocation

    select row_number() over (partition by [date] order by Status)
        as RN,Location,[date],[time],[Status]
        INTO #mydata
    from #myLocation

    SELECT L1.Location 'LocationIn' ,L2.Location 'LocationOut',L1.[date],L1.[Time] 'TimeIn',L2.[Time] 'TimeOut'
    FROM #mydata L1
    INNER JOIN #mydata L2
    ON L1.[date] = L2.[date]
    AND L1.Location <> L2.Location
AND L1.[Status]= 'In'

这篇关于如何合并来自同一个表的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！