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

Introduction

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

Summary

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