SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Join two records in one tabel to make 1 record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

175 Posts

Posted - 04/30/2013 :  21:38:33  Show Profile  Reply with Quote
I have inherited a table that has data which is similar on the next record

ie

Field1 FIELD2 FIELD3
ORIG RECORD JOHN
RTN RECORD JOHN 123456

How can I call that as a select statement to give me
ORIG RECORD JOHN RTN RECORD JOHN 123456

Pete_N
Posting Yak Master

175 Posts

Posted - 04/30/2013 :  22:56:42  Show Profile  Reply with Quote
Maybe if I expand a bit it would help,

The table inherited, can have x number of rows and I want to use that data to populate reports etc on a client baisis.
Each record that has a narrative in field 1 of Orig Record will have a second record with RrnRecord in field 1 ( ie a set ) . Their is an 'recordId' Identity (1,1) field and these records will be sequencial ie recordId for 'orig record' will have id 1, 3 5 etc and 'Rtn Record' would have recorded 2,4,6 etc. There is also a field called reference which is identical for each set of records
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/30/2013 :  23:20:27  Show Profile  Reply with Quote
This is one way to do it:

WITH A as 
(SELECT *, ROW_NUMBER() OVER(Order by ID) as R1 from dbo.TestStub where ID%2 = 1),
B as 
(SELECT *, ROW_NUMBER() OVER(Order by ID) as R2 from dbo.TestStub where ID%2 = 0)
Select A.Field1, A.Field2, A.Field3, B.Field1, B.Field2, B.Field3 from A JOIN B ON R1 = R2;

Edited by - MuMu88 on 04/30/2013 23:29:31
Go to Top of Page

Pete_N
Posting Yak Master

175 Posts

Posted - 04/30/2013 :  23:36:26  Show Profile  Reply with Quote
Brilliant,

Thank you so much. I applied that to the table and the result is perfect !
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/01/2013 :  00:32:43  Show Profile  Reply with Quote
Glad to help.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 05/01/2013 :  23:06:15  Show Profile  Reply with Quote
I think you have to be careful with this. If you have more than one user/proecss then there is no guarantee that the generated IDs will come out in the exact order you hope for.
I think replacing
WHERE IS%2 =1 with WHERE field1='ORIG'
WHERE IS%2 =0 with WHERE field1='RTN'

would be safer.

or even better, just join to itself using the reference, which is exactly what you want:

select A.*, b.*
from
(select * from Table where field1='ORIG') A
inner join
(select * from Table where field1='RTN') B
on (A.reference =B.reference )

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000