Combining More Than One Rows Into a Single Row in SQL Server 2005

In this article I will explain how to merge multiple rows into a single row using SQL 2005. For this use the following steps.

Step 1 : First create the database like:

create database test

use test

Step 2 : Create a table and insert same values in it like:

create table table1(id int,name varchar(20),checktime datetime,status varchar(20))

insert into table1 values(1,'Richa','2012-07-16 13:54:02.000','c/in')

insert into table1 values(1,'Richa','2012-07-16 13:54:35.000','c/out')

insert into table1 values(1,'Richa','2012-07-16 14:21:22.000','c/in')

insert into table1 values(1,'Richa','2012-07-16 14:21:36.000','c/out')

insert into table1 values(1,'Richa','2012-07-17 14:21:53.000','c/in')

insert into table1 values(1,'Richa','2012-07-17 14:41:54.000','c/out')

insert into table1 values(2,'Megha','2012-07-27 16:14:03.000','c/in')

insert into table1 values(2,'Megha','2012-07-27 16:15:23.000','c/out')

Step 3 : Now to see the table write the following command:
 

select * from table1

The result that displays the output is as follows:

Step 4 : Now to merge the data that shows the records for only the userid 1 and 2 into a single row and the date operation write the query as:

with cte_test as

(

select [id],[checktime],[name],[status],ROW_NUMBER() over (partition by [id],[status] order by [id],[status])

 as rn from table1

 )

select a.id,convert(char(10), a.[checktime], 111)as dated,convert(varchar(8),dateadd(s,

sum(datepart(hour, b.[checktime]-a.[checktime]) * 3600) + sum(datepart(minute, b.[checktime]-a.[checktime]) * 60) + sum(datepart(second, b.[checktime]-a.[checktime])),0),108)as timing

 from cte_test a

inner join cte_test b on a.id=b.id and a.name=b.name and convert(char(10), a.[checktime], 111)=convert(char(10), b.[checktime], 111) and a.rn=b.rn

and a.status='c/in' and b.status='c/out'

group by a.Id,convert(char(10), a.[checktime], 111)

Step 5 : Select the preceding query and press F5 to run it, the output is displayed like:

Summary

With the help of this article we can combine multiple rows into a single row based on the values in a column.