Merger in two row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Merger in two row

Author

Topic

faijurrahuman
Starting Member

15 Posts

Posted - 2012-02-27 : 03:48:50

Hi;
I want merge the two rows is not null values

Here my table

id name old new
1 test Null 5
1 test 6 Null

How can i merger the two row

Result format

Id name Old New
1 test 6 5

any one help for this problem..

Share Knowledge team

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-27 : 04:07:54

Try this

SELECT t1.Field1, t2.Field2 FROM Table1 t1 LEFT JOIN Table1 t2 ON t1.FK = t2.FK AND t2.Field1 IS NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-27 : 04:19:31

[code]
select, t1.id, t1.name, old = max(old), new = max(new)
from table1 t1
~~inner join table2 t2 on t1.id = t2.id and t1.name = t2.name~~
group by t1.id, t1.name
[/code]

[edit:] what was i thinking ?

KH
[spoiler]Time is always against us[/spoiler]

sqllearner05
Starting Member

9 Posts

Posted - 2012-02-27 : 05:23:58

instead of using joins and complicating just use below simple query

select id,name,MAX(old),MAX(new) from table1 group by id,name

sqllearner

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-02-27 : 15:29:42

One more solution :

cREATE TABLE #TMP (id INT, name VARCHAR(20),
old INT,NEW INT)

---Insert your records in #TMP

;WITH CTE AS
(

SELECT ROW_NUMBER() OVER( PARTITION BY ID ORDER BY ID )AS RN ,* FROM #TMP
)

SELECT * INTO #TMP2 FROM CTE

UPDATE t2 SET T2.OLD = T1.OLD

FROM #TMP2 T1 INNER JOIN #TMP2 T2 ON t1.RN = T2.RN+1

select * from #TMP2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources