how to merge rows from same table

Question

4.00/5 (1 vote)

See more:

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

Posted 11-Jun-14 22:25pm

Codes DeCodes

Updated 11-Jun-14 23:07pm

Nirav Prabtani

v2

Add a Solution

Comments

PrakashCs.net 12-Jun-14 4:34am

on what basis you are going to map LocationIn and location LocationOut ?

Codes DeCodes 12-Jun-14 4:38am

by common date.. like if date is 2014-6-6. then by status "in" location xyz is shown as location in and of status "out" on that date location "abc" is shown as location out..

Thava Rajan 12-Jun-14 9:57am

is your table has only two records for every group

2 solutions

Solution 1

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

SQL

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'

Posted 11-Jun-14 23:06pm

PrakashCs.net

Updated 11-Jun-14 23:09pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jaket-cp · Accepted Answer · 2014-06-12T06:41:00

You can perform a join on the table to itself.

The following sql should get what you desire:

SQL

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:

SQL

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.

SQL

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