如何合并同一个表中的两列 [英] how to merge two column from same table

问题描述

我想将同一表的两列合并为一列并仅显示选定的列.我有这样的 sql 表.

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

推荐答案

试试这个:

SELECT ISNULL(MAX(CASE WHEN Status = 'In' THEN Location END),'') AS LocationIn,
       ISNULL(MAX(CASE WHEN Status = 'Out' THEN Location END),'') AS LocationOut,
       date,
       ISNULL(MAX(CASE WHEN Status = 'In' THEN time END),'') AS TimeIn,
       ISNULL(MAX(CASE WHEN Status = 'Out' THEN time END),'') AS TimeOut
FROM TableName
GROUP BY date

结果:

LOCATIONIN  LOCATIONOUT   DATE                          TIMEIN   TIMEOUT
xyz         abc           June, 06 2014 00:00:00+0000   10:55    4:30
mno         mop           June, 07 2014 00:00:00+0000   11:00    4:00
abc                       June, 08 2014 00:00:00+0000   11:00   

小提琴示例

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