How can we get records in single row in SQL server using SQL/TSQL

Question

-- Source

Create table staging
(id int,
 name varchar(50),
 empid int,
 company_id int,
 addres varchar(50)
)

Create table Destination
(id int,
 name varchar(50),
 empid int,
 company_id int,
 addres varchar(50)
)

insert into staging
select 1, 'amit',NULL,101,'USA'
UNION ALL
Select 1,'amit',10002,'','USA'
UNION ALL
Select 2,'Vijay','',650,'China'
UNION ALL
Select 2,'Vijay','','','China'
UNION ALL
Select 5,'Sanjay',589756,NULL,'India'
UNION ALL
Select 5,'Sanjay',NULL,151215,'India'


Select * from staging

-- Expected result

-- Destination table

id  name    empid   company_id  addres
1   amit    10002   101         USA
2   Vijay   0       650         China
5   Sanjay  589756  151215      India

It would look like GROUP BY is the solution, though the question is if, for instance, you may have a record like 1,'amit',44444,'','USA'. — FDavidov
– FDavidov, Commented Dec 17, 2016 at 11:17

Mahedi Sabuj · Accepted Answer · 2016-12-17 11:36:04Z

1

Use Group By and MAX

insert into destination
select id, max(name), max(empid), max(company_id), max(addres) from staging
group by id

Result:

edited Dec 17, 2016 at 11:36

answered Dec 17, 2016 at 11:30

Mahedi Sabuj

2,9543 gold badges18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gajanan Over a year ago

It will be time consuming there are 3 Min rows in staging

Collectives™ on Stack Overflow

How can we get records in single row in SQL server using SQL/TSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related