Click here to Skip to main content
15,948,339 members
Please Sign up or sign in to vote.
3.50/5 (2 votes)
See more:
Hi,
Can anyone tell me How to combine two rows into one.

EMPCode	    actHours	        actDate		rowno
107	    8.00		2013-01-21 	1
107	    8.30		2013-01-22 	1
107	    4.00		2013-01-23 	1
107	    4.00		2013-01-23 	2
107	    4.10		2013-01-24 	1
107	    4.20		2013-01-24 	2
107	    4.30		2013-01-25 	1
107	    4.30		2013-01-25 	2

I want the result as follows:

actHours	   actDate	
8.00		   2013-01-21 
8.30		   2013-01-22
8.00		   2013-01-23 
8.30		   2013-01-24
9.00		   2013-01-25 	


Any help will be appreciated. Thanks in advance :-)
Posted
Comments
Maciej Los 26-Mar-13 6:42am    
Describe your logic...
We can't read in your mind...
[no name] 26-Mar-13 6:48am    
Here you are only selecting one column you are not combining.
[no name] 26-Mar-13 6:49am    
Please explain it more briefly.

Hi,

Try below solution


SQL
select
 convert (int , ( sum( (CONVERT(INT, actHours) * 60 ) + ( ( actHours - (CONVERT(INT, actHours) ) )  * 100 ) )/60 ) ) +

( ( sum( (CONVERT(INT, actHours) * 60 ) + ( ( actHours - (CONVERT(INT, actHours) ) )  * 100 ) )/60 )-
 convert (int , ( sum( (CONVERT(INT, actHours) * 60 ) + ( ( actHours - (CONVERT(INT, actHours) ) )  * 100 ) )/60 ) ) ) * .60,
 MAX(actDate)
FROM TABLE_1 GROUP BY actDate
 
Share this answer
 
Comments
Saroj Kumar Sahu 26-Mar-13 7:34am    
Thanks Kasim.
Shanalal Kasim 26-Mar-13 7:49am    
Thanks
Maciej Los 26-Mar-13 7:43am    
Good job, +5!
Shanalal Kasim 26-Mar-13 7:49am    
Thanks
If I have got this correnctly :

SELECT SUM(actHours) as actHours, actDate
FROM *YourTableName*
GROUP BY actDate
 
Share this answer
 
v2
Comments
satz_770 26-Mar-13 7:32am    
This one is PERFECT!!!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900