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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

181 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

181 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

549 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

181 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

549 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  
 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.05 seconds. Powered By: Snitz Forums 2000