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. For this use the following steps.

Step 1: First create the database.

create database test
use test

Step 2. Create a table and insert the same values in it.

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,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 and 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 this.



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

Similar Articles