SELECT m.*,Agt_1,Agt_2,...,Agt_10
FROM
(
SELECT MEM_NBR,LN_NBR,
[1] AS Collateral_1,
[2] AS Collateral_2,
[3] AS Collateral_3,
...
[10] AS Collateral_10
FROM (SELECT MEM_NBR,LN_NBR,rn,ID FROM R) r1
PIVOT (MAX(ID) FOR rn IN ([1],[2],[3],..,[10]))p
)m
INNER JOIN 
(
SELECT MEM_NBR,LN_NBR,
[1] AS Agt_1,
[2] AS Agt_2,
[3] AS Agt_3,
...
[10] AS Agt_10
FROM (SELECT MEM_NBR,LN_NBR,rn,AN FROM R) r2
PIVOT (MAX(AN) FOR rn IN ([1],[2],[3],..,[10]))q
)n
ON n.MEM_NBR = m.MEM_NBR
AND n.LN_NBR = m.LN_NBR

quote:

---

Originally posted by murrayb3024

For every item (Collateral_x and Agt_x) do I have to do a separate select?

If that is the case I can do it this way:

max(case when R.rn = 1 then R.COLLATERAL_ID end) as Collateral_1,
max(case when R.rn = 1 then R.AGT end) as Agt_1,
max(case when R.rn = 1 then R.NextItem end) as NextItem_1,
max(case when R.rn = 1 then R.NextItem end) as NextItem_1,
max(case when R.rn = 2 then R.COLLATERAL_ID end) as Collateral_2,
max(case when R.rn = 2 then R.AGT end) as AGt_2,
max(case when R.rn = 1 then R.NextItem end) as NextItem_2,
max(case when R.rn = 1 then R.NextItem end) as NextItem_2,

I was hoping to cut down the number of statments using variables in a sp, so is that not possible?

---