January 12, 2013 at 6:32 am
in my table i have the records like
Id Record1 Record2 Level gid
1testRec1 testRec2 0 1
2testRec11 testRec22 0 2
3testRec111 testRec222 0 3
4testRec111 testRec2222 0 4
5newtestRec newtestRec2 1 1
I want to show records like
Id OldRecord1 NewRecord1 OldRecord2 NewRecord2
1 testRec1 newtestRec1 testRec2 newtestRec2
2 testRec11 NoNewRecord testRec22 NoNewRecord
3 testRec22 NoNewRecord testRec33 NoNewRecord
4 testRec33 NoNewRecord testRec33 NoNewRecord
actually i have 2 querys
this is for old record
select * from tablename where level=0
this is for New record
select * from tablename where level=1
I want to merge this into a single record set as old and new..plz help
January 12, 2013 at 8:17 am
suvo.kundu (1/12/2013)
in my table i have the records likeId Record1 Record2 Level gid
1testRec1 testRec2 0 1
2testRec11 testRec22 0 2
3testRec111 testRec222 0 3
4testRec111 testRec2222 0 4
5newtestRec newtestRec2 1 1
I want to show records like
Id OldRecord1 NewRecord1 OldRecord2 NewRecord2
1 testRec1 newtestRec1 testRec2 newtestRec2
2 testRec11 NoNewRecord testRec22 NoNewRecord
3 testRec22 NoNewRecord testRec33 NoNewRecord
4 testRec33 NoNewRecord testRec33 NoNewRecord
actually i have 2 querys
this is for old record
select * from tablename where level=0
this is for New record
select * from tablename where level=1
I want to merge this into a single record set as old and new..plz help
SELECT *
FROM tablename old
INNER JOIN tablename new
ON old.id = new.id
WHERE old.LEVEL = 0
AND new.LEVEL = 1;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 12, 2013 at 8:23 am
ya i could get my solution as
select
ZZ.gid,
ZZ.Record1 as 'OldRecord1',
CASE WHEN XX.Record1 IS NULL THEN 'No New Record' ELSE XX.Record1 END as 'NewRecord1' ,
ZZ.Record2 as 'OldRecord2',
CASE WHEN XX.Record2 IS NULL THEN 'No New Record' ELSE XX.Record2 END as 'NewRecord2'
--XX.Record1 as 'NewRecord1',
--XX.Record2 as 'NewRecord2'
from
(
SELECT
ROW_NUMBER ( ) OVER ( order by id )as RN,gid,Record1,Record2 from tblName
where Level=0
)ZZ left outer join
(
SELECT
ROW_NUMBER ( ) OVER ( order by id )as RNN,
Record1,
Record2
from tblName
where Level=1
)XX on xx.rnn=zz.rn
January 12, 2013 at 8:25 am
Good to hear
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply