如何在两个不同的列中划分数据库表的单个列？具有相同的ID [英] How to divide the single column of a database table in two different columns? with same ID

问题描述

我有一个名为Time的列，另一列名为ID。

我想将列Time分为两个不同的列InTime& OutTime。

但重要的是，如果ID列中存在的ID的计数是偶数，则列Time的ID处的相应值将被插入名为OutTime&的列中。 ;如果该ID的计数是奇数，那么列时间ID的相应值将被插入名为InTime的列中。



即如果值是这样的

 插入 #temp 值（< span class =code-digit> 1 ，'  09:30'）
 插入 #temp 值（ 1 ，'  12:30'）
 插入 #temp 值（ 1 ，'   18:30'）

我想要数据为

 ID Intime Outtime 
 1 09:30 12:30 
 1 Null 18:30 

U rgent ..

请帮助我，因为我是这个领域的新手.....

先谢谢你。

解决方案

你需要了解PIVOT / UNPIVOT



http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx [ ^ ]



或



创建两个select语句并将它们用作派生表。然后你可以加入ID并获得你的输出。



ie

 选择 a.Id，a。时间 InTime，b。时间 OutTime 
 来自 
（选择 id，< span class =code-keyword> time  来自 #Temp 其中 .... ..）a 
  inner   join  
（选择 id，时间 来自 #Temp 其中 ......）b 
   a.Id = b.Id 

I have one column named as Time with another column named as ID.
I want to divide the column Time in two different columns as InTime & OutTime.
But the important thing is that if the count of that ID present in a ID column is even, the corresponding value present at that ID of column Time will be inserted in a column named as OutTime & if the count of that ID is odd the corresponding value present at that ID of column Time will be inserted in a column named as InTime.

ie if values are like this

Insert #temp values(1,'09:30') 
Insert #temp values(1,'12:30') 
Insert #temp values(1,'18:30')

I want data as

ID      Intime         Outtime
1       09:30          12:30
1       Null           18:30

Its Urgent..
Please help me as I''m new in this field.....
THANK YOU in Advance.

解决方案

You need to either understand PIVOT / UNPIVOT

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

or

Create two select statements and use them as derived tables. Then you can join on ID and get your output.

i.e.

Select a.Id, a.Time InTime, b.Time OutTime
from
(select id, time from #Temp where ......) a
inner join
(select id, time from #Temp where ......) b
on a.Id = b.Id

这篇关于如何在两个不同的列中划分数据库表的单个列？具有相同的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！