CODEWITHSUNDEEP
sqldatabasemergehiveql
HiveUser
HiveUser

Reputation: 31

How do I merge database rows based on common values?

I've got a table like this:

Col1   Col2   Col3   Col4   Col5
====   ====   ====   ====   ====
 A1     B1     C1    null   null
 A1    null    C1    null    E1
 A1     B2    null    D1     E2
 A1    null    C2    null    E2

How do I merge the information so that I end up with:

Col1   Col2   Col3   Col4   Col5
====   ====   ====   ====   ====
 A1     B1     C1    null    E1
 A1     B2     C2     D1     E2

What SQL will do this for me? Notice the two C1's allow the first 2 rows to be merged, and the two E2's allow the last 2 rows to be merged.

Upvotes: 3

Views: 87

Answers (1)

404 Not found
404 Not found

Reputation: 201

Hope this helps..

CREATE TABLE #LocalTempTable(
col1 varchar(50) ,
col2 varchar(50) NuLL, 
col3 varchar(50) NuLL,
col4 varchar(50) NuLL, 
col5 varchar(50) NuLL)

insert into #LocalTempTable values ( 'A1', 'B1','C1',NULL,NULL);
insert into #LocalTempTable values ( 'A1', NULL,'C2',NULL,'E1');
insert into #LocalTempTable values ( 'A1', 'B2',NULL,'D1','E2');
insert into #LocalTempTable values ( 'A1', NULL,'C2',NULL,'E2');


select * from #LocalTempTable


 --A1     B1     C1    null    E1
 --A1     B2     C2     D1     E2
 select  *from(
select  ROW_NUMBER() over  ( order by lc1.col1  ) as ID ,
lc1.col1
,lc1.col2
,lc1.col3
,lc1.col4
,lc2.col5
 from #LocalTempTable lc1 inner join #LocalTempTable lc2 on lc2.col1=lc1.col1 ) as c where ID=5
 union 

 select  *from(
select  ROW_NUMBER() over  ( order by lc1.col1  ) as ID ,
lc1.col1
,isnull(lc1.col2,lc2.col2) as col2
,isnull(lc2.col3,lc1.col3) as col3
,isnull(lc1.col4,lc2.col4) as col4
,lc2.col5
 from #LocalTempTable lc1 inner join #LocalTempTable lc2 on lc2.col1=lc1.col1 ) as c where ID=10

Upvotes: 1

Related Questions