how to merge two column from same table

问题:

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

回答1:

Try this:

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

Result:

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   

Fiddle Example