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 2005 Forums
 Transact-SQL (2005)
 Finding Match when using Row_Number()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 11/14/2012 :  12:02:03  Show Profile  Reply with Quote
I have this query:

with R as (
select LC.MEMBER, CT.COLLATERAL_ID, row_number() over(partition by LC.MEMBER order by CT.COLLATERAL_ID) as rn
from dbo.Table1 LC inner join dbo.Table2 as CT on LC.COLLATERAL_ID = CT.COLLATERAL_ID
)

SELECT
MEMBER
,max(case when rn = 1 then COLLATERAL_ID end) as Collateral_1
,max(case when rn = 2 then COLLATERAL_ID end) as Collateral_2
,max(case when rn = 3 then COLLATERAL_ID end) as Collateral_3
,max(case when rn = 4 then COLLATERAL_ID end) as Collateral_4
,max(case when rn = 5 then COLLATERAL_ID end) as Collateral_5
,max(case when rn = 6 then COLLATERAL_ID end) as Collateral_6
,max(case when rn = 7 then COLLATERAL_ID end) as Collateral_7
,max(case when rn = 8 then COLLATERAL_ID end) as Collateral_8
,max(case when rn = 9 then COLLATERAL_ID end) as Collateral_9
,max(case when rn = 10 then COLLATERAL_ID end) as Collateral_10
from R
group by MEMBER


So each collateral "could" have a title. I need to keep it in one row, but I am trying to do is add another column with a title number for each collateral (Null if there isn't one) from table3 where collateral_IDs match. I have tried unsuccessfully to do this and get the correct title number matches.

Edited by - murrayb3024 on 11/14/2012 12:12:21

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/14/2012 :  12:54:33  Show Profile  Reply with Quote
Can't you add a JOIN to table3 in your cte definition on collateral_id returning title? Then add 10 more of these:
,max(case when rn = 1 then [Title] end) as Title_1

Be One with the Optimizer
TG
Go to Top of Page

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 11/14/2012 :  13:10:33  Show Profile  Reply with Quote
Tried this earlier:

with R as (
select LC.MEMBER, CT.COLLATERAL_ID, row_number() over(partition by LC.MEMBER order by CT.COLLATERAL_ID) as rn, LCI.TITLE
from dbo.Table1 LC inner join dbo.Table2 as CT on LC.COLLATERAL_ID = CT.COLLATERAL_ID
INNER JOIN dbo.LCollat as LCI on LCI.COLLATERAL_ID = LC.COLLATERAL_ID
)

SELECT
MEMBER
,max(case when rn = 1 then COLLATERAL_ID end) as Collateral_1
,max(case when rn = 1 then TITLE end) as TITLE_1
,max(case when rn = 2 then COLLATERAL_ID end) as Collateral_2
,max(case when rn = 1 then TITLE end) as TITLE_2...

Returns the same title for all of them
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/14/2012 :  13:25:01  Show Profile  Reply with Quote
Then LCollat must have many Collateral_id values for each title?

select collateral_id, count(distinct title) from LCollat group by collateral_id having count(distinct title) > 1
and/or vice-versa
select title, count(distinct collateral_id) from LCollat group by title having count(distinct collateral_id) > 1

Be One with the Optimizer
TG
Go to Top of Page

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 11/14/2012 :  14:10:26  Show Profile  Reply with Quote
I had messed it up on my row_number and used the wrong field, when I fixed that it worked. Thanks
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.05 seconds. Powered By: Snitz Forums 2000