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 2005 Forums
 Transact-SQL (2005)
 Finding Match when using Row_Number()
 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
6065 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
6065 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  
 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.06 seconds. Powered By: Snitz Forums 2000