CODEWITHSUNDEEP
sqlsql-server
user3444185
user3444185

Reputation: 9

how to get one row data value into two row data value using SQL server?

could you help me

select emp_id Emp ID,start_time timing,last_time timing
from data 
where (emp_id=5500 and date_id='3/18/2014')

Actual Output is,

Emp ID  timing                       timing
5500    03/18/2014 18:30:08             03/18/2014 19:23:09

I need the following output

Emp ID    timing
5500    03/18/2014 18:30:08
5500    03/18/2014 19:23:09

how to get this output? please help me, i dont know how to get this data. thanks for advance!!!

Upvotes: 0

Views: 48

Answers (4)

AK47
AK47

Reputation: 3797

try to remove where clause. For multiple rows, it will work!

select emp_id Emp ID, start_time timing
from data 

union all

select emp_id Emp ID, last_time timing
from data

Upvotes: 0

Mala
Mala

Reputation: 1249

IF you have many rows then usng Union all or cross join will take time .. Alternately you can select two differeent query like

select emp_id Emp ID, start_time timing from data where (emp_id=5500 and date_id='3/18/2014')

select emp_id Emp ID, last_time timing from data where (emp_id=5500 and date_id='3/18/2014')

from code behind using generic list you can merge the both list object like

List a = new List(); List b = new List();

a.AddRange(b);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

An alternative way to do this is with unpivot. The following typically has about the same performance:

select emp_id as "Emp ID",
       (case when n.n = 1 then start_time
             else last_time
        end) as timing
from data cross join
     (select 1 as n union all select 2) n
where emp_id = 5500 and date_id = '2014-03-18';

Upvotes: 0

Szymon
Szymon

Reputation: 43023

You could just union two selects:

select emp_id Emp ID, start_time timing
from data 
where (emp_id=5500 and date_id='3/18/2014')

union all

select emp_id Emp ID, last_time timing
from data 
where (emp_id=5500 and date_id='3/18/2014')

Upvotes: 3

Related Questions