Join two records in one tabel to make 1 record

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 2012 Forums

Transact-SQL (2012)

Join two records in one tabel to make 1 record

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-04-30 : 21:38:33

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 - 2013-04-30 : 22:56:42

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-30 : 23:20:27

This is one way to do it:
[CODE]
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;
[/CODE]

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-04-30 : 23:36:26

Brilliant,

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-01 : 00:32:43

Glad to help.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-05-01 : 23:06:15

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 )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources