本文介绍了如何合并同一个表中的两列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将同一表的两列合并为一列并仅显示选定的列.我有这样的 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
小提琴示例知识库>
这篇关于如何合并同一个表中的两列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!