2 Rows into 1 Row (NOT PIVOT) - 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 2005 Forums

Transact-SQL (2005)

2 Rows into 1 Row (NOT PIVOT)

Author

Topic

snanto
Starting Member

2 Posts

Posted - 2007-11-01 : 17:20:42

I have 2 rows of data that look like this:

BORROWERID LOANID TYPE NAME
123 102 BRW1 JOHN DOE
124 102 BRW2 JANE DOE

I need a query which will return this:

LOANID BORROWERID COBORROWERID BORROWER_NAME COBORROWER_NAME
102 123 124 JOHN DOE JANE DOE

Can anyone help me?

Thanks,

Shawn

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-01 : 18:59:22

Will there always be max 2 records per loanid?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

snanto
Starting Member

2 Posts

Posted - 2007-11-01 : 19:19:57

In most of the cases, yes, but there is another table I have to flatten that looks like this:

STATUSTYPE DATESENT DATERECEIVED
ESCROW 1/1/2003 1/3/2003
TITLE 12/1/2003 12/3/2003
CLOSED 2/3/2003 2/5/2003
...

Transformed to this:
EscrowDateSent EscrowDateReceived TitleDateSent TitleDateReceived ...

and so on for each row in the statuses table.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-01 : 19:32:03

[code]
Select T.LOANID,
T.BORROWERID,
T2.BORROWERID As COBORROWERID,
T.NAME As BORROWER_NAME,
T2.Name As COBORROWER_NAME
From @t T
JOIN (Select *
FROM @t T1
WHERE T1.TYPE = 'BRW2'
) T2 ON T2.LoanId = T.LOANID
WHERE T.TYPE = 'BRW1'
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources