| Author |
 Topic  |
|
|
murrayb3024
Yak Posting Veteran
66 Posts |
 Posted - 11/14/2012 : 12:02:03
|
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
5469 Posts |
Posted - 11/14/2012 : 12:54:33
|
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 |
 |
|
|
murrayb3024
Yak Posting Veteran
66 Posts |
Posted - 11/14/2012 : 13:10:33
|
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 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/14/2012 : 13:25:01
|
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 |
|
|
|
murrayb3024
Yak Posting Veteran
66 Posts |
Posted - 11/14/2012 : 14:10:26
|
| I had messed it up on my row_number and used the wrong field, when I fixed that it worked. Thanks |
|
|
| |
Topic |
|
Finding Match when using Row_Number()
